Re: pg_dump out of memory for large table with LOB

[Adrien Nayrat]

Hello,

On 11/10/18 12:49 AM, Jean-Marc Lessard wrote:
> The dumped table is 0.5TB, 17 million rows and LOB uses about 99% of the 
> space.
> 

If I understand, you have 17 million Large Object?

I do not recall exactly and maybe I am wrong. But it seems pg_dump has to
allocate memory for each object to dump :
addBoundaryDependencies:

for (i = 0; i < numObjs; i++)
[...]

case DO_BLOB_DATA:
/* Data objects: must come between the boundaries */
addObjectDependency(dobj, preDataBound->dumpId);
addObjectDependency(postDataBound, dobj->dumpId);
break;

addObjectDependency:

[...]
pg_malloc(dobj->allocDeps * sizeof(DumpId));


With 17 million LO, it could eat lot of memory ;)



signature.asc
Description: OpenPGP digital signature

Re: Fwd: Log file

[Peter J. Holzer]

On 2018-10-29 14:56:06 -0400, Tom Lane wrote:
> Igor Korot  writes:
> > Or I will have to change the owner/group manuall every time I will
> > access the file?
> 
> You can set up the log files as readable by the OS group of the server
> (see log_file_mode), and then grant membership in that group to whichever
> OS accounts you trust.

Another way is to use ACLs. Set a default ACL on the log directory which
enforces read permission for some users or groups on all newly created
files.

This is a bit more fine-grained and may be better suited for situations
where different people should be able to read the logs of different
servers. I also find that ACLs are more likely to survive "corrective"
actions by update or init scripts.

Another way would be to log via syslog and configure syslogd to split
the log files according to the username embedded in the log message.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

query patterns for multipass aggregating

[Rob Nikander]

Hi,

I’ve run into this pattern a few times, and I usually get a little confused. 
I’m wondering if there are some common solutions or techniques.

Simplified example:

I have tables `items`, `colors`, and `images`. Items have many colors, and many 
images.

I want a query to list items, with their colors and images. Each result row is 
an item, so the colors and images must be aggregated into arrays or json. 

If there were only one other table, it’s a simple join and group…

select items.*, array_agg(color_name)
from items join colors on items.id = colors.item_id
group by items.id

Now to get images too, my first try uses a CTE… 

// Tables:
// items (id, name, foo)
// colors (item_id, color_name, color_foo)
// images (item_id, image_file, image_foo)

with items_with_colors as (
  // This is the same query as above 
  select items.*, array_agg(colors.color_name) as color_names
  from items
  join colors on items.id = colors.item_id
  group by items.id
)
// Same idea repeated but now joining to images table
select items.*, array_agg(images.image_file) as image_files
from items_with_colors items
join images on items.id = images.item_id
group by items.id, items.name, items.foo, items.color_names;  // mmm :(

One first problem is on the last line. I have to list out all the columns. In 
my real situation there are many more. Listing them is only an inconvenience, 
but I’m more worried that it is internally comparing all the columns, when 
really it could compare only `items.id`.

So… are there some other techniques that usually (or might) work better? It 
seems like the kind of thing that could run in parallel, if I wrote it right. 

Rob

Re: query patterns for multipass aggregating

[Andrew Gierth]

> "Rob" == Rob Nikander  writes:

 Rob> I want a query to list items, with their colors and images. Each
 Rob> result row is an item, so the colors and images must be aggregated
 Rob> into arrays or json.

 Rob> If there were only one other table, it’s a simple join and group…

 Rob> select items.*, array_agg(color_name)
 Rob> from items join colors on items.id = colors.item_id
 Rob> group by items.id

Method 1:

select items.*, c.colors, i.images
  from items
   left join (select item_id, array_agg(color_name) as colors
from colors
   group by item_id) c
 on c.item_id=items.id
   left join (select item_id, array_agg(image) as images
from images
   group by item_id) i
 on i.item_id=items.id;

Method 2:

select items.*, c.colors, i.images
  from items
   left join lateral (select array_agg(c0.color_name) as colors
from colors c0
   where c0.item_id=items.id) c
 on true
   left join lateral (select array_agg(i0.image) as images
from images i0
   where i0.item_id=items.id) i
 on true;

Unfortunately, the planner isn't smart enough yet to know that these two
are equivalent, so they generate different sets of possible query plans.
Method 1 gets plans that work well if the entire items table is being
selected, since it will read the whole of the images and colors tables
in one go, and it will also get plans that work well for reading a
_single_ item selected by WHERE items.id=? because equivalence-class
processing will propagate a copy of that condition down to below the
grouping clauses. It will _not_ get a good plan for reading any other
small subset of items (e.g. selected by other criteria); for this you
need method 2, which in turn doesn't get very good plans when you fetch
the whole items table.

Don't be tempted to use CTEs for the subqueries in either plan; that'll
only make it much worse.

-- 
Andrew (irc:RhodiumToad)

Re: query patterns for multipass aggregating

[Andrew Gierth]

> "Andrew" == Andrew Gierth  writes:

 Andrew> Unfortunately, the planner isn't smart enough yet to know that
 Andrew> these two are equivalent,

oops, I edited the second one before posting in a way that made them not
be equivalent: adding a "group by x0.item_id" in both subqueries in
method 2 makes them equivalent again. Without that, the result differs
slightly if there are no matching color or image rows (NULL vs. empty
array).

-- 
Andrew (irc:RhodiumToad)

Re: query patterns for multipass aggregating

[Rob Nikander]

> On Nov 11, 2018, at 12:54 PM, Andrew Gierth  
> wrote:
> …

Thank you that is very helpful.

Could the CTE’s theoretically be optimized in a future version of PG, to work 
like the subqueries? I like to use them to give names to intermediate results, 
but I’ll stay away from them for now.

Rob

Re: query patterns for multipass aggregating

[Ondřej Bouda]

Dne 11.11.2018 v 17:20 Rob Nikander napsal(a):
> I have tables `items`, `colors`, and `images`. Items have many 
colors, and many images.

>
> I want a query to list items, with their colors and images. Each 
result row is an item, so the colors and images must be aggregated into 
arrays or json.


What about subqueries?

SELECT
items.*,
(SELECT array_agg(color_name) FROM colors WHERE item_id = items.id) 
AS color_names,
(SELECT array_agg(image_file) FROM images WHERE item_id = items.id) 
AS image_files

FROM items

According to my experience, not only the code is readable (no questions 
about how many rows are there for each item), but it also leads to a 
better query plan.



Dne 11.11.2018 v 19:23 Rob Nikander napsal(a):

Could the CTE’s theoretically be optimized in a future version of PG, to work 
like the subqueries? I like to use them to give names to intermediate results, 
but I’ll stay away from them for now.


It is on the todo list (for quite some time already):
https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/
Until then, using CTEs in situations as yours leads to poor query plans 
as Postgres will compute the whole CTE separately (i.e., all rows 
involved) and only then picks some of them to the inner table.


Regards,
Ondřej Bouda

Re: Copy data from DB2 (Linux) to PG

[Florian Bachmann]

On 01.11.2018 18:27, Ravi Krishna wrote:

I have a project to develop a script/tool to copy data from DB2 to PG.  The 
approach I am thinking is

1. Export data from db2 in a text file, with, say pipe as delimiter.
2. Load the data from the text file to PG using COPY command.

In order to make it faster I can parallelize export and load with upto X number 
of tables concurrently.

Is there a tool in which I can avoid first exporting and then loading.  I think 
one way I can do it is by
using pipes by which I can export from db2 to a pipe and simultaneously load it 
to PG using COPY.

Any other tool for this job?

thanks.



Haven't tried it myself, but you may be able to connect the DB2 database 
to your PostgreSQL cluster using this FDW module: 
https://github.com/wolfgangbrandl/db2_fdw


Then you could just use INSERT INTO ... SELECT  statements to do the ETL 
process with the necessary type conversions and whatnot.


Looks like db2_fdw is DB2 LUW only though, so you might be out of luck 
if your DB2 is on IBM i (or z ;-)


Kind regards
Florian

Re: query patterns for multipass aggregating

[Andrew Gierth]

> "Ondřej" == Ondřej Bouda  writes:

 Ondřej> What about subqueries?

 Ondřej> SELECT
 Ondřej> items.*,
 Ondřej> (SELECT array_agg(color_name) FROM colors WHERE item_id =
 Ondřej> items.id) AS color_names,
 Ondřej> (SELECT array_agg(image_file) FROM images WHERE item_id =
 Ondřej> items.id) AS image_files
 Ondřej> FROM items

 Ondřej> According to my experience, not only the code is readable (no
 Ondřej> questions about how many rows are there for each item), but it
 Ondřej> also leads to a better query plan.

This is (generally speaking) no improvement over the LATERAL method I
showed, and is less flexible (for example it's very painful to return
more than one value from the subqueries).

-- 
Andrew (irc:RhodiumToad)

Move cluster to new host, upgraded version

[Rich Shepard]

  My current desktop server/workstation is running version 10.5. I'm
configuring a replacement desktop and have installed version 11.1 on it. To
copy all databases from the 10.5 version to the 11.1 version I assume that I
should do a pg_dumpall on the current host and read in that file on the
replacement host. Is this the proper procedure?

TIA,

Rich

Re: Move cluster to new host, upgraded version

[Andrew Gierth]

> "Rich" == Rich Shepard  writes:

 Rich> My current desktop server/workstation is running version 10.5.
 Rich> I'm configuring a replacement desktop and have installed version
 Rich> 11.1 on it. To copy all databases from the 10.5 version to the
 Rich> 11.1 version I assume that I should do a pg_dumpall on the
 Rich> current host and read in that file on the replacement host. Is
 Rich> this the proper procedure?

The most reliable and preferred procedure is to use the _new_ version's
pg_dumpall, for example by allowing access to the old host from the new
one (possibly using an ssh port forward), or (on OSes that make it easy
to do package installs of multiple versions) to install the new pg_dump
and pg_dumpall on the old system.

Using the old version's pg_dumpall also generally speaking works, but
there may be occasional rough edges.

-- 
Andrew (irc:RhodiumToad)

Re: Move cluster to new host, upgraded version

[Adrian Klaver]

On 11/11/18 11:21 AM, Rich Shepard wrote:

   My current desktop server/workstation is running version 10.5. I'm
configuring a replacement desktop and have installed version 11.1 on it. To
copy all databases from the 10.5 version to the 11.1 version I assume 
that I

should do a pg_dumpall on the current host and read in that file on the
replacement host. Is this the proper procedure?


See Andrew's reply for pg_dumpall advice.

Not sure if you have any extensions or not, but the part I often skip is 
installing extensions in the new cluster before running the dump restore.




TIA,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Move cluster to new host, upgraded version

[Rich Shepard]

On Sun, 11 Nov 2018, Adrian Klaver wrote:


Not sure if you have any extensions or not, but the part I often skip is
installing extensions in the new cluster before running the dump restore.


  Thanks, Adrian. No extensions here.

Regards,

Rich

Re: Copy data from DB2 (Linux) to PG

[Ravi Krishna]

>Haven't tried it myself, but you may be able to connect the DB2 database 
>to your PostgreSQL cluster using this FDW module: 
>https://github.com/wolfgangbrandl/db2_fdw
>Looks like db2_fdw is DB2 LUW only though, so you might be out of luck 
>if your DB2 is on IBM i (or z ;-)

As the thread indicates, I am using DB2 Linux.
I used Carto one with ODBC and it is just not production ready.  Found two bugs 
within days of
testing.  I have created bugs in the github.


>Then you could just use INSERT INTO ... SELECT  statements to do the ETL 
>process with the necessary type conversions and whatnot.

I am currently using Unix pipes to export data from DB2 and concurrently load 
it to PG via COPY.
It saves nearly half the time of exporting first to file and then loading it.

Re: Move cluster to new host, upgraded version

[Adrian Klaver]

On 11/11/18 12:18 PM, Rich Shepard wrote:

On Sun, 11 Nov 2018, Andrew Gierth wrote:


The most reliable and preferred procedure is to use the _new_ version's
pg_dumpall, for example by allowing access to the old host from the new
one (possibly using an ssh port forward), or (on OSes that make it 
easy to

do package installs of multiple versions) to install the new pg_dump and
pg_dumpall on the old system.


Andrew,

   Using the SlackBuilds.org script with an updated version number 
worked for

11.1. So, I'll install it on the old desktop and upgrade in place. Then I
can use the 11.1 pg_dumpall on the data directory and copy that over to the
new desktop.


pg_dumpall is going to need to run against a Postgres server not just a 
data directory. If both your old and new machines are on the same 
network, why not just point the 11.1 pg_dumpall(on the new machine) at 
the 9.5 server running on the old machine?




Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Move cluster to new host, upgraded version

[Rich Shepard]

On Sun, 11 Nov 2018, Andrew Gierth wrote:


The most reliable and preferred procedure is to use the _new_ version's
pg_dumpall, for example by allowing access to the old host from the new
one (possibly using an ssh port forward), or (on OSes that make it easy to
do package installs of multiple versions) to install the new pg_dump and
pg_dumpall on the old system.


Andrew,

  Using the SlackBuilds.org script with an updated version number worked for
11.1. So, I'll install it on the old desktop and upgrade in place. Then I
can use the 11.1 pg_dumpall on the data directory and copy that over to the
new desktop.

Thanks,

Rich

Re: Move cluster to new host, upgraded version

[Rich Shepard]

On Sun, 11 Nov 2018, Adrian Klaver wrote:

pg_dumpall is going to need to run against a Postgres server not just a data 
directory.


Adrian,

  Of course. Yet it's the data directory that's written to the .sql file.


If both your old and new machines are on the same network, why not just
point the 11.1 pg_dumpall(on the new machine) at the 9.5 server running on
the old machine?


  The old host is running 10.5. Haven't tried to run an application on one
host using data on another host. I'll look at which tool will do that.

Thanks,

Rich

Re: Move cluster to new host, upgraded version

[Rich Shepard]

On Sun, 11 Nov 2018, Rich Shepard wrote:


Haven't tried to run an application on one host using data on another
host. I'll look at which tool will do that.


  Looks like the pg_dumpall '-h' option will act on the other host's data
directory.

Regards,

Rich

Re: Move cluster to new host, upgraded version

[Adrian Klaver]

On 11/11/18 12:51 PM, Rich Shepard wrote:

On Sun, 11 Nov 2018, Adrian Klaver wrote:

pg_dumpall is going to need to run against a Postgres server not just 
a data directory.


Adrian,

   Of course. Yet it's the data directory that's written to the .sql file.


In order for pg_dumpall to access the data directory the Postgres server 
has to be running. In your previous post you said you where going to 
upgrade in place, not sure what that means. That seemed to be overkill 
and given your posts from a week(?) or so ago, possibly a problem.





If both your old and new machines are on the same network, why not just
point the 11.1 pg_dumpall(on the new machine) at the 9.5 server 
running on

the old machine?


   The old host is running 10.5. Haven't tried to run an application on one
host using data on another host. I'll look at which tool will do that.


pg_dump(all) are clients designed to do just that. I and many others do 
it all the time. Supply the proper host information and you should be 
golden.




Thanks,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Move cluster to new host, upgraded version

[Adrian Klaver]

On 11/11/18 12:53 PM, Rich Shepard wrote:

On Sun, 11 Nov 2018, Rich Shepard wrote:


Haven't tried to run an application on one host using data on another
host. I'll look at which tool will do that.


   Looks like the pg_dumpall '-h' option will act on the other host's data
directory.


No it will work on the other hosts's Postgres server which in turn will 
pull from it's data directory.




Regards,

Rich





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Move cluster to new host, upgraded version

[Ron]

On 11/11/2018 02:51 PM, Rich Shepard wrote:

On Sun, 11 Nov 2018, Adrian Klaver wrote:

pg_dumpall is going to need to run against a Postgres server not just a 
data directory.


Adrian,

  Of course. Yet it's the data directory that's written to the .sql file.


Unless your db is small, do a parallel dump.  Even then, do a "-Fc" backup 
instead.  That's been the recommended method for many years.



--
Angular momentum makes the world go 'round.

pg9.6 when is a promoted cluster ready to accept "rewind" request?

[magodo]

Dear supporters,

I'm writing some scripts to implement manual failover. I have two
clusters(let's say p1 and p2), where one is primary(e.g. p1) and the
other is standby(e.g. p2). The way to do manual failover is straight
forward, like following:

1. promote on p2
2. wait `pg_is_ready()` on p2
3. rewind on p1
4. prepare a recovery.conf on p1
5. start p1

This should ends up with the same HA but role switched.

It works find if I manually do each step. 

But if I call each step sequentially in a script, it will fail after I
switched role for the 1st time and want to switch back.

For example, with a fresh setup(timeline starts from 1), I firstly
tried to switch role, and it works. I get p1 as standby following p2,
which is the priamry. Then I switch role again and error occurs, the
error message is like:

   < 2018-11-12 04:59:24.547 UTC > LOG:  entering standby mode
   < 2018-11-12 04:59:24.555 UTC > LOG:  redo starts at 0/428
   < 2018-11-12 04:59:24.566 UTC > LOG:  started streaming WAL from
   primary at 0/500 on timeline 1
   < 2018-11-12 04:59:24.566 UTC > FATAL:  could not receive data from
   WAL stream: ERROR:  requested WAL segment 00020005
   has already been
   removed 
  

   < 2018-11-12 04:59:24.577 UTC > LOG:  started streaming WAL from
   primary at 0/500 on timeline 1
   < 2018-11-12 04:59:24.577 UTC > FATAL:  could not receive data from
   WAL stream: ERROR:  requested WAL segment 00020005
   has already been
   removed 
  

   < 2018-11-12 04:59:25.413 UTC > FATAL:  the database system is
   starting up
   < 2018-11-12 04:59:26.416 UTC > FATAL:  the database system is
   starting up
   < 2018-11-12 04:59:27.419 UTC > FATAL:  the database system is
   starting up
   < 2018-11-12 04:59:28.422 UTC > FATAL:  the database system is
   starting up
   < 2018-11-12 04:59:29.425 UTC > FATAL:  the database system is
   starting up
   < 2018-11-12 04:59:29.576 UTC > LOG:  started streaming WAL from
   primary at 0/500 on timeline 1
   < 2018-11-12 04:59:29.576 UTC > FATAL:  could not receive data from
   WAL stream: ERROR:  requested WAL segment 00020005
   has already been removed  


the pg_rewind output is as follow:

   servers diverged at WAL position 0/560 on timeline 1 
   rewinding from last common checkpoint at 0/460 on timeline 1 

>From the log, it seems the wrong timeline of divergence is evaluated,
it should be timeline 2 rather than 1. 

Furthermore, if I add a `sleep` between step 2(promote) and step
3(rewind), it just works. 

Hence, I suspect the promoted cluster is not ready to be used for
rewinding right after promote. Is there anything I need to wait before
I rewind the old primary against this promoted cluster?

Thank you in advance!

---
magodo