Re: Rapid disk usage spikes when updating large tables with GIN indexes

[Jonathan Marks]

I believe that we have figured it out. It indeed was a WAL issue — the WAL 
wasn’t getting measured because it had been moved into an archived folder.

We resolved this in a two main ways:

1. By dramatically increasing max_wal_size to decrease the frequency of commits
2. By turning on wal_compression

We’re going to continue looking into whether we can reduce wal generation / 
write volume further by turning back on fast_update on our GIN indexes.

> On May 16, 2018, at 4:31 PM, Nikolay Samokhvalov  
> wrote:
> 
> Why not set up a spot EC2 instance with Postgres 10.1, load database from a 
> dump (yes you’ll need to create one from RDS because they don’t provide 
> direct access to dumps/backups; probably you need to get only specific 
> tables) and repeat your actions, closely looking at filesystem.
> 
> ср, 16 мая 2018 г. в 13:10, Jonathan Marks  >:
> Hi Tom —
> 
> We turned on log_temp_files and since the last stats reset (about a week ago) 
> we’re seeing 0 temp files altogether (grabbing that info from 
> pg_stat_database).
> 
> So, as far as we know:
> 
> 1) It’s not WAL
> 2) It’s not tempfiles
> 3) It’s not the size of the error logs
> 4) It’s not the size of the actual rows in the database or the indexes
> 
> Another thread we found suggested pg_subtrans — this seems less likely 
> because we’ve been able to replicate this across many different types of 
> connections etc. but thought it might be a potential source.
> 
> Any other system-monitoring queries that we can run that might further 
> illuminate the issue?
> 
> Thank you!
> 
> > On May 14, 2018, at 3:31 PM, Jonathan Marks  > > wrote:
> > 
> > We’ll turn on log_temp_files and get back to you to see if that’s the 
> > cause. Re: the exact queries — these are just normal INSERTs and UPDATEs. 
> > This occurs as part of normal database operations — i.e., we are processing 
> > 10% of a table and marking changes to a particular row, or happen to be 
> > inserting 5-10% of the table volume with new rows. Whenever we bulk load we 
> > have to drop the indexes because the disk space loss just isn’t tenable.
> > 
> > Re: extra disk space consumption not within PG — the AWS folks can’t tell 
> > me what the problem is because it’s all internal to the PG part of the 
> > instance they can’t access. Doesn’t mean your last suggestion can’t be the 
> > case but makes it slightly less likely.
> > 
> > Any chance that GIN indexes are double-logging? I.e. with fastupdate off 
> > they are still trying to keep track of the changes in the pending list or 
> > something?
> > 
> > Our thought has been temp files for a while, but we’re not sure what we 
> > should do if that turns out to be the case.
> > 
> >> On May 14, 2018, at 3:08 PM, Tom Lane  >> > wrote:
> >> 
> >> [ please keep the list cc'd ]
> >> 
> >> Jonathan Marks  >> > writes:
> >>> Thanks for your quick reply. Here’s a bit more information:
> >>> 1) to measure the “size of the database” we run something like `select 
> >>> datname, pg_size_pretty(pg_database_size(datname)) from pg_database;` I’m 
> >>> not sure if this includes WAL size.
> >>> 2) I’ve tried measuring WAL size with `select sum(size) from 
> >>> pg_ls_waldir();` — this also doesn’t budge.
> >>> 3) Our current checkpoint_timeout is 600s with a 
> >>> checkpoint_completion_target of 0.9 — what does that suggest?
> >> 
> >> Hmph.  Your WAL-size query seems on point, and that pretty much destroys
> >> my idea about a WAL emission spike.
> >> 
> >> pg_database_size() should include all regular and temporary tables/indexes
> >> in the named DB.  It doesn't include WAL (but we've eliminated that), nor
> >> cluster-wide tables such as pg_database (but those seem pretty unlikely
> >> to be at issue), nor non-relation temporary files such as sort/hash temp
> >> space.  At this point I think we have to focus our attention on what might
> >> be creating large temp files.  I do not see anything in the GIN index code
> >> that could do that, especially not if you have fastupdate off.  I wonder
> >> whether there is something about the particular bulk-insertion queries
> >> you're using that could result in large temp files --- which'd make the
> >> apparent correlation with GIN index use a mirage, but we're running out
> >> of other ideas.  You could try enabling log_temp_files to see if there's
> >> anything to that.
> >> 
> >> In the grasping-at-straws department: are you quite sure that the extra
> >> disk space consumption is PG's to begin with, rather than something
> >> outside the database entirely?
> >> 
> >>  regards, tom lane
> > 
> 
> 

Re: posgresql.log

[Ron]

On 05/21/2018 04:40 PM, Bartosz Dmytrak wrote:


Hi Gurus,

Looking into my postgresql.log on one of my test servers I found scary entry:

--2018-05-19 05:28:21-- http://207.148.79.161/post0514/post

Connecting to 207.148.79.161:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 1606648 (1.5M) [application/octet-stream]

Saving to: ‘/var/lib/postgresql/10/main/postgresq1’

0K .. .. .. .. ..  3% 71.0K 21s

    50K .. .. .. .. ..  6% 106K 17s

   100K .. .. .. .. ..  9% 213K 13s

   150K .. .. .. .. .. 12% 213K 11s


[snip]

1500K .. .. .. .. .. 98% 11.8M 0s

  1550K ..  100% 12.5M=2.6s

2018-05-19 05:28:25 (598 KB/s) - ‘/var/lib/postgresql/10/main/postgresq1’ 
saved [1606648/1606648]


Downloaded file is not posgresql but postgresq1(one).

It was pure pg instalation without any contrib modules addons etc, 
istalled on ubuntu box by apt manager using repos:


http://apt.postgresql.org/pub/repos/apt xenial-pgdg/main

http://apt.postgresql.org/pub/repos/apt xenial-pgdg

I have never seen such entry on other my other servers…

Could you be so kind and explain me what is it? I am afraid my postgres 
has been hacekd.




This looks like what happens when the adobe flash player package downloads 
the closed-source binary installer.  Thus, I wouldn't be surprised if the 
repository package isn't downloading the installation binaries from 
http://207.148.79.161/post0514/post.


--
Angular momentum makes the world go 'round.

Re: posgresql.log

[Steve Atkins]

> On May 21, 2018, at 3:21 PM, Steve Crawford  
> wrote:
> 
> 
> 
> If this is a test server and you can take it offline for forensics I would do 
> so, especially if it could provide a path to other internal or critical 
> resources. If you can image it for safekeeping and forensics, even better.

+1

It's compromised. Image it if possible; save the compromise payload you know 
about if not.

Treat it as compromised and unsafe to attach to a network until you completely 
wipe and reinstall it.

> 
> That appears to be output from wget but the intrusion, if any, could be 
> through any number of vectors (web, ssh, local attack, etc.) not directly 
> related to PostgreSQL. Check in your other logs starting with a search for 
> anything related to that IP address.

It's probably a compromise via postgresql open to the network with insecure 
settings. I've seen several of those reported recently, and this one is saving 
it's payload to the postgresql data directory - somewhere no other user or app 
will have access to, but which a compromised postgresql can easily write to.

Check the pg_hba.conf and packet filter / firewall settings and see what the 
issue may be. Do the same checks on all your other postgresql servers, test and 
production. If there's a configuration mistake that let one server be 
compromised it's may well be there on others too.

> 
> Verify the usual. Patches up to date, ports appropriately firewalled off, no 
> default passwords, etc.
> 
> IP comes back to vultr.com which is a cloud company (i.e. could be anyone) 
> but if it is an attack perhaps contact their abuse department.

The C server there is already down; It can't hurt to notify them, but I doubt 
Choopa would be particularly interested beyond that point unless a subpoena or 
search warrant were involved.

> Unless you are positive the server was not attacked, don't trust it unless 
> you can be absolutely certain it is clean. Best bet is to backup any critical 
> data (and check it for trustworthiness), wipe and rebuild.

+1.

> 
> Only you (well, OK, maybe them, now) know what data was on this server but 
> depending on its importance, internal policies, legal requirements and 
> agreements with third-parties you may have notification requirements and 
> could need to engage forensics experts.

Cheers,
  Steve

Re: posgresql.log

[Steve Crawford]

On Mon, May 21, 2018 at 2:40 PM Bartosz Dmytrak  wrote:

> Hi Gurus,
>
> Looking into my postgresql.log on one of my test servers I found scary
> entry:
>
>
>
> --2018-05-19 05:28:21--  http://207.148.79.161/post0514/post
>
> Connecting to 207.148.79.161:80... connected.
>
> HTTP request sent, awaiting response... 200 OK
>
> Length: 1606648 (1.5M) [application/octet-stream]
>
> Saving to: ‘/var/lib/postgresql/10/main/postgresq1’
>
>
>
>  0K .. .. .. .. ..  3% 71.0K
> 21s
>
> 50K .. .. .. .. ..  6%  106K
> 17s
>
>100K .. .. .. .. ..  9%  213K
> 13s
>
>150K .. .. .. .. .. 12%  213K
> 11s
>
>200K .. .. .. .. .. 15% 16.3M 9s
>
>250K .. .. .. .. .. 19%  215K 8s
>
>300K .. .. .. .. .. 22% 15.6M 7s
>
>350K .. .. .. .. .. 25% 11.7M 6s
>
>400K .. .. .. .. .. 28%  219K 5s
>
>450K .. .. .. .. .. 31% 12.1M 5s
>
>500K .. .. .. .. .. 35% 11.7M 4s
>
>550K .. .. .. .. .. 38% 12.2M 3s
>
>600K .. .. .. .. .. 41% 12.1M 3s
>
>650K .. .. .. .. .. 44%  228K 3s
>
>700K .. .. .. .. .. 47% 12.2M 3s
>
>750K .. .. .. .. .. 50% 12.1M 2s
>
>800K .. .. .. .. .. 54% 11.7M 2s
>
>850K .. .. .. .. .. 57% 12.1M 2s
>
>900K .. .. .. .. .. 60% 11.8M 2s
>
>950K .. .. .. .. .. 63% 12.1M 1s
>
>   1000K .. .. .. .. .. 66% 12.0M 1s
>
>   1050K .. .. .. .. .. 70%  243K 1s
>
>   1100K .. .. .. .. .. 73% 12.1M 1s
>
>   1150K .. .. .. .. .. 76% 12.1M 1s
>
>   1200K .. .. .. .. .. 79% 11.7M 1s
>
>   1250K .. .. .. .. .. 82% 12.1M 1s
>
>   1300K .. .. .. .. .. 86% 12.1M 0s
>
>   1350K .. .. .. .. .. 89% 11.8M 0s
>
>   1400K .. .. .. .. .. 92% 12.1M 0s
>
>   1450K .. .. .. .. .. 95% 12.1M 0s
>
>   1500K .. .. .. .. .. 98% 11.8M 0s
>
>   1550K ..    100%
> 12.5M=2.6s
>
>
>
> 2018-05-19 05:28:25 (598 KB/s) - ‘/var/lib/postgresql/10/main/postgresq1’
> saved [1606648/1606648]
>
>
>
> Downloaded file is not posgresql but postgresq1(one).
>
>
>
> It was pure pg instalation without any contrib modules addons etc,
> istalled on ubuntu box by apt manager using repos:
>
> http://apt.postgresql.org/pub/repos/apt xenial-pgdg/main
>
> http://apt.postgresql.org/pub/repos/apt xenial-pgdg
>
>
>
> I have never seen such entry on other my other servers…
>
> Could you be so kind and explain me what is it? I am afraid my postgres
> has been hacekd.
>
>
>
>
>
> Best Regards
>
> *Bartosz Dmytrak*
>

If this is a test server and you can take it offline for forensics I would
do so, especially if it could provide a path to other internal or critical
resources. If you can image it for safekeeping and forensics, even better.

That appears to be output from wget but the intrusion, if any, could be
through any number of vectors (web, ssh, local attack, etc.) not directly
related to PostgreSQL. Check in your other logs starting with a search for
anything related to that IP address.

Verify the usual. Patches up to date, ports appropriately firewalled off,
no default passwords, etc.

IP comes back to vultr.com which is a cloud company (i.e. could be anyone)
but if it is an attack perhaps contact their abuse department.

Unless you are positive the server was not attacked, don't trust it unless
you can be absolutely certain it is clean. Best bet is to backup any
critical data (and check it for trustworthiness), wipe and rebuild.

Only you (well, OK, maybe them, now) know what data was on this server but
depending on its importance, internal policies, legal requirements and
agreements with third-parties you may have notification requirements and
could need to engage forensics experts.

Good luck,
Steve

Re: posgresql.log

[Adrian Klaver]

On 05/21/2018 02:40 PM, Bartosz Dmytrak wrote:

Hi Gurus,

Looking into my postgresql.log on one of my test servers I found scary 
entry:


Is there a Web app running on this server?

The log entries below are from the Postgres logs in?:

/var/log/postgresql/



--2018-05-19 05:28:21--  http://207.148.79.161/post0514/post

Connecting to 207.148.79.161:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 1606648 (1.5M) [application/octet-stream]


Hmm, the below says it downloaded 12.5M.



Saving to: ‘/var/lib/postgresql/10/main/postgresq1’


The postgresq1 file is actually there?

If so have you looked at the file:

file postgresq1

to get an idea of what it is?



0K .. .. .. .. ..  3% 71.0K 21s

     50K .. .. .. .. ..  6%  
106K 17s


    100K .. .. .. .. ..  9%  
213K 13s


    150K .. .. .. .. .. 12%  
213K 11s


    200K .. .. .. .. .. 15% 16.3M 9s

    250K .. .. .. .. .. 19%  215K 8s

    300K .. .. .. .. .. 22% 15.6M 7s

    350K .. .. .. .. .. 25% 11.7M 6s

    400K .. .. .. .. .. 28%  219K 5s

    450K .. .. .. .. .. 31% 12.1M 5s

    500K .. .. .. .. .. 35% 11.7M 4s

    550K .. .. .. .. .. 38% 12.2M 3s

    600K .. .. .. .. .. 41% 12.1M 3s

    650K .. .. .. .. .. 44%  228K 3s

    700K .. .. .. .. .. 47% 12.2M 3s

    750K .. .. .. .. .. 50% 12.1M 2s

    800K .. .. .. .. .. 54% 11.7M 2s

    850K .. .. .. .. .. 57% 12.1M 2s

    900K .. .. .. .. .. 60% 11.8M 2s

    950K .. .. .. .. .. 63% 12.1M 1s

   1000K .. .. .. .. .. 66% 12.0M 1s

   1050K .. .. .. .. .. 70%  243K 1s

   1100K .. .. .. .. .. 73% 12.1M 1s

   1150K .. .. .. .. .. 76% 12.1M 1s

   1200K .. .. .. .. .. 79% 11.7M 1s

   1250K .. .. .. .. .. 82% 12.1M 1s

   1300K .. .. .. .. .. 86% 12.1M 0s

   1350K .. .. .. .. .. 89% 11.8M 0s

   1400K .. .. .. .. .. 92% 12.1M 0s

   1450K .. .. .. .. .. 95% 12.1M 0s

   1500K .. .. .. .. .. 98% 11.8M 0s

   1550K ..  100% 12.5M=2.6s

2018-05-19 05:28:25 (598 KB/s) - 
‘/var/lib/postgresql/10/main/postgresq1’ saved [1606648/1606648]


Downloaded file is not posgresql but postgresq1(one).

It was pure pg instalation without any contrib modules addons etc, 
istalled on ubuntu box by apt manager using repos:


http://apt.postgresql.org/pub/repos/apt xenial-pgdg/main

http://apt.postgresql.org/pub/repos/apt xenial-pgdg

I have never seen such entry on other my other servers…

Could you be so kind and explain me what is it? I am afraid my postgres 
has been hacekd.


Best Regards

*/Bartosz Dmytrak/*




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

posgresql.log

[Bartosz Dmytrak]

Hi Gurus,

Looking into my postgresql.log on one of my test servers I found scary entry:

 

--2018-05-19 05:28:21--  http://207.148.79.161/post0514/post

Connecting to 207.148.79.161:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 1606648 (1.5M) [application/octet-stream]

Saving to: ‘/var/lib/postgresql/10/main/postgresq1’

 

 0K .. .. .. .. ..  3% 71.0K 21s

50K .. .. .. .. ..  6%  106K 17s

   100K .. .. .. .. ..  9%  213K 13s

   150K .. .. .. .. .. 12%  213K 11s

   200K .. .. .. .. .. 15% 16.3M 9s

   250K .. .. .. .. .. 19%  215K 8s

   300K .. .. .. .. .. 22% 15.6M 7s

   350K .. .. .. .. .. 25% 11.7M 6s

   400K .. .. .. .. .. 28%  219K 5s

   450K .. .. .. .. .. 31% 12.1M 5s

   500K .. .. .. .. .. 35% 11.7M 4s

   550K .. .. .. .. .. 38% 12.2M 3s

   600K .. .. .. .. .. 41% 12.1M 3s

   650K .. .. .. .. .. 44%  228K 3s

   700K .. .. .. .. .. 47% 12.2M 3s

   750K .. .. .. .. .. 50% 12.1M 2s

   800K .. .. .. .. .. 54% 11.7M 2s

   850K .. .. .. .. .. 57% 12.1M 2s

   900K .. .. .. .. .. 60% 11.8M 2s

   950K .. .. .. .. .. 63% 12.1M 1s

  1000K .. .. .. .. .. 66% 12.0M 1s

  1050K .. .. .. .. .. 70%  243K 1s

  1100K .. .. .. .. .. 73% 12.1M 1s

  1150K .. .. .. .. .. 76% 12.1M 1s

  1200K .. .. .. .. .. 79% 11.7M 1s

  1250K .. .. .. .. .. 82% 12.1M 1s

  1300K .. .. .. .. .. 86% 12.1M 0s

  1350K .. .. .. .. .. 89% 11.8M 0s

  1400K .. .. .. .. .. 92% 12.1M 0s

  1450K .. .. .. .. .. 95% 12.1M 0s

  1500K .. .. .. .. .. 98% 11.8M 0s

  1550K ..    100% 12.5M=2.6s

 

2018-05-19 05:28:25 (598 KB/s) - ‘/var/lib/postgresql/10/main/postgresq1’ saved 
[1606648/1606648]

 

Downloaded file is not posgresql but postgresq1(one).

 

It was pure pg instalation without any contrib modules addons etc, istalled on 
ubuntu box by apt manager using repos:

http://apt.postgresql.org/pub/repos/apt xenial-pgdg/main

http://apt.postgresql.org/pub/repos/apt xenial-pgdg

 

I have never seen such entry on other my other servers…

Could you be so kind and explain me what is it? I am afraid my postgres has 
been hacekd. 

 

 

Best Regards

Bartosz Dmytrak

 

Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

[Alexey Dokuchaev]

On Mon, May 21, 2018 at 12:54:56PM -0400, Tom Lane wrote:
> Alexey Dokuchaev  writes:
> > Quick reality check question: are count(*) vs. count(_) equivalent
> > above?
> 
> Only if _ is guaranteed non-null ... which, as a rowtype result, it
> probably is.  But I'd use count(*) if you're concerned about speed.

Understood.  Yes, my primary concern (after correctness) is speed.  Thank
you all again for very helpful and prompt replies guys.

./danfe

Re: Will Altering and Modifying tables during backup result in a corrupted server after the restore?

[Stephen Frost]

Greetings,

* Christoph Moench-Tegeder (c...@burggraben.net) wrote:
> ## Yashwanth Govinda Setty (ygovindase...@commvault.com):
> 
> >   2.  Restore the server with transaction logs
> 
> This is missing a lot of details. If you do it right - see your email
> thread from one week ago - you will be able to recover the database
> server to a state as of the _end_ of the backup process (as marked by
> the return of the pg_stop_backup() command).
> If you do not follow the backup/restore documentation to the letter,
> the database will be corrupted and will not start (sometimes people
> report with haphazard backup schemes, but that's just more luck than
> they deserve, and nobody should rely on that).

Please also note that the PG documentation provided, when it comes to
the examples, are purely for usage demonstration only and shouldn't be
considered a good idea when it comes to implementing an actual solution.

Using only "cp" for archive_command is a particularly bad idea as it
doesn't sync the file to disk.  Be sure to also heed the recommendation
about using the non-exclusive backup method and *not* using the
exclusive backup method.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

[Tom Lane]

Alexey Dokuchaev  writes:
> Quick reality check
> question: are count(*) vs. count(_) equivalent above?

Only if _ is guaranteed non-null ... which, as a rowtype result, it
probably is.  But I'd use count(*) if you're concerned about speed.

regards, tom lane

Re: Will Altering and Modifying tables during backup result in a corrupted server after the restore?

[Christoph Moench-Tegeder]

## Yashwanth Govinda Setty (ygovindase...@commvault.com):

>   2.  Restore the server with transaction logs

This is missing a lot of details. If you do it right - see your email
thread from one week ago - you will be able to recover the database
server to a state as of the _end_ of the backup process (as marked by
the return of the pg_stop_backup() command).
If you do not follow the backup/restore documentation to the letter,
the database will be corrupted and will not start (sometimes people
report with haphazard backup schemes, but that's just more luck than
they deserve, and nobody should rely on that).

Regards,
Christoph

-- 
Spare Space

Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

[Alexey Dokuchaev]

On Mon, May 21, 2018 at 12:20:52PM -0400, Tom Lane wrote:
> Alexey Dokuchaev  writes:
> > On Mon, May 21, 2018 at 10:45:27AM -0400, Tom Lane wrote:
> >> OP could do something like
> >>select json_agg(_) as j, count(*) as c INTO r FROM (
> 
> > Thanks, I've arrived at the same solution (using local RECORD) eventually.
> > It works as intended, but I still need to assign OUT parameters by hand:
> 
> > retcode := tmp.c;
> > result := tmp.j;
> 
> Actually, it should work to do
> 
>   select json_agg(_), count(*) INTO result, retcode FROM ...

Bingo!  Thanks Tom, you're the best (as always).  Quick reality check
question: are count(*) vs. count(_) equivalent above?  I vaguely recall
that count(1) got somewhat slower than count(*) after 8.2, but cannot
back it up right now.

./danfe

Re: Will Altering and Modifying tables during backup result in a corrupted server after the restore?

[Stephen Frost]

Greetings,

* Yashwanth Govinda Setty (ygovindase...@commvault.com) wrote:
> 1.   Creating a big table. Identify the physical file on the disk.
> 1.  While backup process is backing up a file associated with the table - 
> update the rows , add a column.
> 2.  Restore the server with transaction logs
> 
> We are backing up (copying) the entire postgres data directory. The 
> database/table file being backed up (copied), is modified by running 
> alter/update queries on the table.
> When we restore the copied data directory and replay/apply the transaction 
> logs, will the server be restored to an healthy state?

You haven't really spelled out your actual backup process but it
certainly sounds like it's lacking.

> (The files modified during backup can be corrupted, will this affect the 
> restore?)

With a proper PG-style filesystem-backup, any corruption due to ongoing
writes from PG will be handled by the transaction log.

To perform a proper PG-style filesystem-backup, you need to:

- Ensure that WAL is being archived somewhere.  This can be done with
  archive_command or with pg_receivewal.  All WAL archived during the
  backup *must* be saved or the backup will be inconsistent and
  incomplete (and, therefore, basically useless).

- Make sure to run pg_start_backup() before you begin copying *any*
  files

- Make sure to run pg_stop_backup() after you have copied all files

- Verify that all of the WAL generated between the pg_start_backup()
  call and the pg_stop_backup() call have been archived.  The
  information about what WAL is needed is returned from those calls.

- On restore, create and populate the backup_label file in the data
  directory to indicate that it was a backup being restored.
  (Alternatively, create that file during the backup itself and store it
  in the backup system, to be restored when the backup is restored).

Ideally, you'd also verify the page-level checksums (if they're enabled)
when doing the backup, calculate your own checksum of the file (to
detect if it gets corrupted between the backup time and the restore
time) and verify that everything is physically written out to permanent
storage before claiming to have a successful backup.

Further information is available here:

https://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-BASE-BACKUP

Generally speaking, however, I would strongly discourage people from
trying to write yet-another-PG-backup-tool, there are several already,
my favorite being pgbackrest, and contributing to one of them would be
a better approach.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

[Tom Lane]

Alexey Dokuchaev  writes:
> On Mon, May 21, 2018 at 10:45:27AM -0400, Tom Lane wrote:
>> OP could do something like
>>  select json_agg(_) as j, count(*) as c INTO r FROM (

> Thanks, I've arrived at the same solution (using local RECORD) eventually.
> It works as intended, but I still need to assign OUT parameters by hand:

> retcode := tmp.c;
> result := tmp.j;

Actually, it should work to do

select json_agg(_), count(*) INTO result, retcode FROM ...

regards, tom lane

Re: Will Altering and Modifying tables during backup result in a corrupted server after the restore?

[Alvaro Aguayo Garcia-Rada]

Not sure if such case would corrup the backup, but I won't make a backup just 
by copying the data directory.

Use pg_basebackup instead, it's safer

Regards,

Alvaro Aguayo
Operations Manager
Open Comb Systems E.I.R.L.

Office: (+51-1) 3377813 | Mobile: (+51) 995540103 | (+51) 954183248
Web: www.ocs.pe

- Original Message -
From: "Yashwanth Govinda Setty" 
To: "PostgreSql-general" 
Sent: Monday, 21 May, 2018 10:03:18
Subject: Will Altering and Modifying tables during backup result in a corrupted 
server after the restore? 

Hi All,

We are trying this scenario:
Here are the steps being done:

1.   Creating a big table. Identify the physical file on the disk.

  1.  While backup process is backing up a file associated with the table - 
update the rows , add a column.
  2.  Restore the server with transaction logs

We are backing up (copying) the entire postgres data directory. The 
database/table file being backed up (copied), is modified by running 
alter/update queries on the table.
When we restore the copied data directory and replay/apply the transaction 
logs, will the server be restored to an healthy state?
(The files modified during backup can be corrupted, will this affect the 
restore?)

Thanks,
Yashwanth

***Legal Disclaimer***
"This communication may contain confidential and privileged material for the
sole use of the intended recipient. Any unauthorized review, use or distribution
by others is strictly prohibited. If you have received the message by mistake,
please advise the sender by reply email and delete the message. Thank you."
**

Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

[Alexey Dokuchaev]

On Mon, May 21, 2018 at 10:45:27AM -0400, Tom Lane wrote:
> OP could do something like
> 
> declare r record;
> ...
> select json_agg(_) as j, count(*) as c INTO r FROM (
>   SELECT foo, bar, baz ...
>   FROM t1, t2, t3 WHERE ...) AS _;
> 
> This would be slightly more expensive than doing only the one aggregate,
> but it should beat anything involving a temp table.

Thanks, I've arrived at the same solution (using local RECORD) eventually.
It works as intended, but I still need to assign OUT parameters by hand:

retcode := tmp.c;
result := tmp.j;

I'd love to get rid of OUT parameters and return RECORD directly (it also
would make the code more "functional" in style), but then I'd be getting
this annoying ``a column definition list is required for functions
returning "record"'' error which I don't know how to avoid.  Using OUT's
simplifies things for callers (they don't have to annotate the types by
hand) while still preserving type safety.

On Mon, May 21, 2018 at 07:30:44AM -0700, David G. Johnston wrote:
> > ...
> > Is this expected and correct behavior?
>
> Yes, the query returned only one row, with a single json column.  You
> wrote the equivalent of:
>
> SELECT json_agg(...) FROM ... INTO result;
>
> And you are getting the count of the top-most select (which is implied
> in the syntax that you used).

I see, thanks; I've neglected the fact that there's still another implicit
SELECT being involved.

./danfe

Will Altering and Modifying tables during backup result in a corrupted server after the restore?

[Yashwanth Govinda Setty]

Hi All,

We are trying this scenario:
Here are the steps being done:

1.   Creating a big table. Identify the physical file on the disk.

  1.  While backup process is backing up a file associated with the table - 
update the rows , add a column.
  2.  Restore the server with transaction logs

We are backing up (copying) the entire postgres data directory. The 
database/table file being backed up (copied), is modified by running 
alter/update queries on the table.
When we restore the copied data directory and replay/apply the transaction 
logs, will the server be restored to an healthy state?
(The files modified during backup can be corrupted, will this affect the 
restore?)

Thanks,
Yashwanth

***Legal Disclaimer***
"This communication may contain confidential and privileged material for the
sole use of the intended recipient. Any unauthorized review, use or distribution
by others is strictly prohibited. If you have received the message by mistake,
please advise the sender by reply email and delete the message. Thank you."
**

Re: Installing PostgreSQL as non-root by Extracting the binaries from RPMs

[Alvaro Aguayo Garcia-Rada]

Should work with no problem, but I would not recommend it. You may not be able 
to setup a service(SysV or systemd) to automatically start postgres, and 
updating version could be troublesome.

Regards,

Alvaro Aguayo
Operations Manager
Open Comb Systems E.I.R.L.

Office: (+51-1) 3377813 | Mobile: (+51) 995540103 | (+51) 954183248
Web: www.ocs.pe

- Original Message -
From: "Thiagarajan Lakshminarayanan" 
To: pgsql-general@lists.postgresql.org
Sent: Monday, 21 May, 2018 09:23:28
Subject: Installing PostgreSQL as non-root by Extracting the binaries from RPMs

Hello,

In our environment, our standard RHEL OS install comes with all the
PostgreSQL required packages / libraries installed.  So, can I just extract
the binaries from PostgreSQL RPMs via rpm2cpio and create a tar file and
install it as non-root user in an alternate location?  I tested the above
approach, created a new PostgreSQL instance and it is working fine.  Please
let me know if there would be any issues with the above approach?

Thanks
Raj

Re: Installing PostgreSQL as non-root by Extracting the binaries from RPMs

[Tom Lane]

Thiagarajan Lakshminarayanan  writes:
> Thanks Benjamin.  Actually, we don't use Docker in our environment. We just
> use a standard RHEL OS install. Will there be any issues if I install
> PostgreSQL by extracting the RPM binaries?

What exactly is your purpose here?  Is it just that you want to run a
separate data directory somewhere?  You don't need a separate set of
binaries for that, just point PGDATA to where you want it and run the
server as the owner of that data directory.

(Depending on the details, you might need to teach SELinux to allow
this usage.  I don't remember exactly what cues SELinux to treat a
particular process as being subject to its restrictions, but use of
the system binaries is at least part of it.)

If you want to be able to build and run a server without any root
privileges at all, you might be best off just building from source.
Maybe I'm too used to doing that, but I'm not very clear on what
you gain by extracting the result of somebody else's build -- which,
almost by definition, is not exactly what you want.

regards, tom lane

Re: Installing PostgreSQL as non-root by Extracting the binaries from RPMs

[Thiagarajan Lakshminarayanan]

Thanks Benjamin.  Actually, we don't use Docker in our environment. We just
use a standard RHEL OS install. Will there be any issues if I install
PostgreSQL by extracting the RPM binaries?

Please let me know.

Thanks
Raj

On Mon, May 21, 2018 at 10:15 AM, Benjamin Scherrey <
scher...@proteus-tech.com> wrote:

> You know this is something super easy to do if you do it within Docker.
> Advise you to go ahead and map the data directories to the host file system
> rather than use a storage container until you get a lot more experience
> with Docker. Otherwise it's crazy easy and, in fact, there already exist
> Docker images with Postgres installed that you could just download and use.
> Postgres will be entirely under local user control and requires no system
> level access.
>
>   -- Ben
>
> On Mon, May 21, 2018 at 9:23 PM, Thiagarajan Lakshminarayanan <
> thiagu1...@gmail.com> wrote:
>
>> Hello,
>>
>> In our environment, our standard RHEL OS install comes with all the
>> PostgreSQL required packages / libraries installed.  So, can I just extract
>> the binaries from PostgreSQL RPMs via rpm2cpio and create a tar file and
>> install it as non-root user in an alternate location?  I tested the above
>> approach, created a new PostgreSQL instance and it is working fine.  Please
>> let me know if there would be any issues with the above approach?
>>
>> Thanks
>> Raj
>>
>
>

Re: Installing PostgreSQL as non-root by Extracting the binaries from RPMs

[Benjamin Scherrey]

You know this is something super easy to do if you do it within Docker.
Advise you to go ahead and map the data directories to the host file system
rather than use a storage container until you get a lot more experience
with Docker. Otherwise it's crazy easy and, in fact, there already exist
Docker images with Postgres installed that you could just download and use.
Postgres will be entirely under local user control and requires no system
level access.

  -- Ben

On Mon, May 21, 2018 at 9:23 PM, Thiagarajan Lakshminarayanan <
thiagu1...@gmail.com> wrote:

> Hello,
>
> In our environment, our standard RHEL OS install comes with all the
> PostgreSQL required packages / libraries installed.  So, can I just extract
> the binaries from PostgreSQL RPMs via rpm2cpio and create a tar file and
> install it as non-root user in an alternate location?  I tested the above
> approach, created a new PostgreSQL instance and it is working fine.  Please
> let me know if there would be any issues with the above approach?
>
> Thanks
> Raj
>

Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

[Tom Lane]

"David G. Johnston"  writes:
>> Is it possible to obtain the
>> first ROW_COUNT (after SELECT) without performing it twice?

> ​Not directly, no.  You should execute the inner query to a temporary table
> than perform your counting and json_agg from that.

OP could do something like

declare r record;
...
select json_agg(_) as j, count(*) as c INTO r FROM (
  SELECT foo, bar, baz ...
  FROM t1, t2, t3 WHERE ...) AS _;

This would be slightly more expensive than doing only the one aggregate,
but it should beat anything involving a temp table.

regards, tom lane

Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

[Adrian Klaver]

On 05/21/2018 05:54 AM, Alexey Dokuchaev wrote:

Hi,

I'm seeing somewhat confusing results here with 9.6.8, and cannot find
the answer in the docs or google.

I'm returning JSON array (or any array, it does not make a difference)
from my plpgsql function like this:

 OUT retcode int,
 OUT result json)
 . . .
 result := json_agg(_) FROM (
   SELECT foo, bar, baz ...
   FROM t1, t2, t3 WHERE ...) AS _; -- this works fine

 GET DIAGNOSTICS retcode = ROW_COUNT;   -- always returns 1

I'd expected `retcode' to contain the number of SELECT'ed rows, but it
is something else (always 1).  Apparently, aggregation functions like
json_agg()/array_agg() mangle the ROW_COUNT from the inner SELECT (the
one I'm interested in).

Is this expected and correct behavior?  Is it possible to obtain the
first ROW_COUNT (after SELECT) without performing it twice?  Thanks,


Off the top of my head:

 SELECT count(*) as ct, foo, bar, baz ...

retcode = result ->'ct'



./danfe





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

Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

[David G. Johnston]

On Mon, May 21, 2018 at 5:54 AM, Alexey Dokuchaev  wrote:

> result := json_agg(_) FROM (
>   SELECT foo, bar, baz ...
>   FROM t1, t2, t3 WHERE ...) AS _;  -- this works fine
>
> GET DIAGNOSTICS retcode = ROW_COUNT;-- always returns 1
>
> I'd expected `retcode' to contain the number of SELECT'ed rows, but it
> is something else (always 1).  Apparently, aggregation functions like
> json_agg()/array_agg() mangle the ROW_COUNT from the inner SELECT (the
> one I'm interested in).
>
> Is this expected and correct behavior?


​Yes, the query returned only one row, with a single json column.  You
wrote the equivalent of:

SELECT json_agg(...) FROM ... INTO result;

And you are getting the count of the top-most select (which is implied in
the syntax that you used).


>   Is it possible to obtain the
> first ROW_COUNT (after SELECT) without performing it twice?
>

​Not directly, no.  You should execute the inner query to a temporary table
than perform your counting and json_agg from that.

David J.

Installing PostgreSQL as non-root by Extracting the binaries from RPMs

[Thiagarajan Lakshminarayanan]

Hello,

In our environment, our standard RHEL OS install comes with all the
PostgreSQL required packages / libraries installed.  So, can I just extract
the binaries from PostgreSQL RPMs via rpm2cpio and create a tar file and
install it as non-root user in an alternate location?  I tested the above
approach, created a new PostgreSQL instance and it is working fine.  Please
let me know if there would be any issues with the above approach?

Thanks
Raj

Aggregate functions with FROM clause and ROW_COUNT diagnostics

[Alexey Dokuchaev]

Hi,

I'm seeing somewhat confusing results here with 9.6.8, and cannot find
the answer in the docs or google.

I'm returning JSON array (or any array, it does not make a difference)
from my plpgsql function like this:

OUT retcode int,
OUT result json)
. . .
result := json_agg(_) FROM (
  SELECT foo, bar, baz ...
  FROM t1, t2, t3 WHERE ...) AS _;  -- this works fine

GET DIAGNOSTICS retcode = ROW_COUNT;-- always returns 1

I'd expected `retcode' to contain the number of SELECT'ed rows, but it
is something else (always 1).  Apparently, aggregation functions like
json_agg()/array_agg() mangle the ROW_COUNT from the inner SELECT (the
one I'm interested in).

Is this expected and correct behavior?  Is it possible to obtain the
first ROW_COUNT (after SELECT) without performing it twice?  Thanks,

./danfe

Re: [GENERAL] Postgre compatible version with RHEL 7.5

[David G. Johnston]

On Sun, May 20, 2018 at 10:15 PM, Deepti Sharma S <
deepti.s.sha...@ericsson.com> wrote:

> Hello Team,
>
> Can you please let us know what postgre version is compatible with
> RHEL7.5? We are currently using Postgre version 9.6.6.
>

​9.6.6 is compatible but not supported - the current supported release for
that 9.6 version is ​9.6.9 (more generally, the 5 releases that are shown
on the home page).

David J.

Errors with physical replication

[greigwise]

Hello.  

We are on Postgresql version 9.6.6.  We have 2 EC2 instances in different
Amazon regions and we are doing physical replication via VPN.  It all seems
to work just fine most of the time.   I'm noticing in the logs that we have
recurring erros (maybe 10 or 12 times per day) that look like this:

2018-05-17 06:36:14 UTC  5af0599f.210d  LOG:  invalid resource manager ID 49
at 384/42A4AB00
2018-05-17 06:36:14 UTC  5afd22de.7ac4  LOG:  started streaming WAL from
primary at 384/4200 on timeline 1
2018-05-17 07:20:17 UTC  5afd22de.7ac4  FATAL:  could not receive data from
WAL stream: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Or some that also look like this:

2018-05-17 07:20:17 UTC  5af0599f.210d  LOG:  record with incorrect
prev-link 49F07120/9F100C95 at 384/45209FC0
2018-05-17 07:20:18 UTC  5afd2d31.1889  LOG:  started streaming WAL from
primary at 384/4500 on timeline 1
2018-05-17 08:03:28 UTC  5afd2d31.1889  FATAL:  could not receive data from
WAL stream: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

And some like this:

2018-05-17 23:00:13 UTC  5afd63ec.26fc  LOG:  invalid magic number  in
log segment 00010385003C, offset 10436608
2018-05-17 23:00:14 UTC  5afe097d.49aa  LOG:  started streaming WAL from
primary at 385/3C00 on timeline 1

Then, like maybe once every couple months or so, we have a crash with logs
looking like this:

2018-05-17 08:03:28 UTC hireology 5af47b75.2670 hireology WARNING: 
terminating connection because of crash of another server process
2018-05-17 08:03:28 UTC hireology 5af47b75.2670 hireology DETAIL:  The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
2018-05-17 08:03:28 UTC hireology 5af47b75.2670 hireology HINT:  In a moment
you should be able to reconnect to the database and repeat your command.
2018-05-17 08:03:28 UTC  5af0599f.210a  LOG:  database system is shut down

When this last error occurs, the recovery is to go on the replica and remove
all the WAL logs from the pg_xlog director and then restart Postgresql. 
Everything seems to recover and come up fine.  I've done some tests
comparing counts between the replica and the primary and everything seems
synced just fine from all I can tell.  

So, a couple of questions.  1) Should I be worried that my replica is
corrupt in some way or given that everything *seems* ok, is it reasonable to
believe that things are working correctly in spite of these errors being
reported.  2)  Is there something I should configure differently to avoid
some of these errors?

Thanks in advance for any help.

Greig Wise




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: [GENERAL] Postgre compatible version with RHEL 7.5

[Martín Marqués]

El 21/05/18 a las 02:15, Deepti Sharma S escribió:
> Hello Team,
> 
> Can you please let us know what postgre version is compatible with RHEL7.5? 
> We are currently using Postgre version 9.6.6.

Please, don't hijack other threads just changing the subject line. Start
a new mail thread with the question you have.

As Adrian mentioned, the yum postgres web shows all available version of
postgres for each distribution version.

> See below for full Postgres to RH/Centos/Fedora/SL/etc mapping:
> 
> http://yum.postgresql.org/repopackages.php

In the case of RHEL 7.x, all supported versions will work (and some of
the unsupported ones will as well, although it's not recommended to
still be on versions prior to 9.3).

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

RE: [GENERAL] Postgre compatible version with RHEL 7.5

[Deepti Sharma S]

Hello Team,

Can you please let us know what postgre version is compatible with RHEL7.5? We 
are currently using Postgre version 9.6.6.


DEEPTI SHARMA 
Specialist 
ITIL 2011 Foundation Certified 
BDGS, R

Ericsson
3rd Floor, ASF Insignia - Block B Kings Canyon,
Gwal Pahari, Gurgaon, Haryana 122 003, India
Phone 0124-6243000
deepti.s.sha...@ericsson.com
www.ericsson.com 

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, January 19, 2016 9:39 PM
To: Deepti Sharma S ; pgsql-gene...@postgresql.org
Subject: Re: [GENERAL] Postgre compatible version with RHEL 7.2

On 01/17/2016 05:06 AM, Deepti Sharma S wrote:
> Hello Team,
>
> Can you please confirm if PostgreSQL version 9.4.x is compatible with 
> RHEL 7.2? and if not when the same will be available?

See below for full Postgres to RH/Centos/Fedora/SL/etc mapping:

http://yum.postgresql.org/repopackages.php

To answer your specific question, from above:

PostgreSQL 9.4
 Red Hat Enterprise Linux 7 - x86_64

>
> *Regards///**
> **Deepti Sharma**
> **Sr. Configuration Engineer*
> *(ITIL 2011 Foundation Certified)
> *
>


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

Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat and PostGreSQL 9.2.24 on Debian

[Ian Barwick]

On 05/21/2018 07:18 PM, Jonatan Evald Buus wrote:
> Hi there,
>
> I'm trying to configure streaming replication between a Red Hat server
> running PostGreSQL 9.2.2 and a Debian server running PostGreSQL 9.2.24
> with Hot Standby enabled.
>
> While the base backup works fine using /pg_basebackup/, I get the following
> errors with hot standby enabled on the Debian slave when starting PostGreSQL:

> /WARNING:  WAL was generated with wal_level=minimal, data may be missing/
> /HINT:  This happens if you temporarily set wal_level=minimal without taking 
a new base backup./
> /FATAL:  hot standby is not possible because wal_level was not set to 
"hot_standby" on the master server/
> /HINT:  Either set wal_level to "hot_standby" on the master, or turn off 
hot_standby here/
>
> If I turn Hot Standby off on the Debian Slave I get the following errors:
> /FATAL:  database system identifier differs between the primary and standby/
> /DETAIL:  The primary's identifier is 5940475598986796885, the standby's 
identifier is 6557962695089036503./

The standby clearly hasn't been cloned from the primary, otherwise the
identifiers would be the same. Are you sure the PostgreSQL instance
running on the standby is the one you backed up with pg_basebackup?


Regards

Ian Barwick

--
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Streaming Replication between PostGreSQL 9.2.2 on Red Hat and PostGreSQL 9.2.24 on Debian

[Jonatan Evald Buus]

Hi there,

I'm trying to configure streaming replication between a Red Hat server
running PostGreSQL 9.2.2 and a Debian server running PostGreSQL 9.2.24 with
Hot Standby enabled.

While the base backup works fine using *pg_basebackup*, I get the following
errors with hot standby enabled on the Debian slave when starting
PostGreSQL:
*WARNING:  WAL was generated with wal_level=minimal, data may be missing*
*HINT:  This happens if you temporarily set wal_level=minimal without
taking a new base backup.*
*FATAL:  hot standby is not possible because wal_level was not set to
"hot_standby" on the master server*
*HINT:  Either set wal_level to "hot_standby" on the master, or turn off
hot_standby here*

If I turn Hot Standby off on the Debian Slave I get the following errors:
*FATAL:  database system identifier differs between the primary and standby*
*DETAIL:  The primary's identifier is 5940475598986796885, the standby's
identifier is 6557962695089036503.*

Configuring streaming replication with Hot Standby enabled locally on the
Red Hat server works like a charm, and I've thus concluded that the issues
must be one of the following:
- PostGreSQL's minor version differs between the 2 servers
- The two servers are running different operating systems

Any insight or guidance as to achieve this scenario would be greatly
appreciated.

Cheers
Jona