Thread: Q: extract database name from directory dump

Hi,

I have been searching but haven't been able to find the
answer to the following question:

How can I (programmatically) find out which database a dump
was taken from given the dump file ?

Constraints of the question:

- existing dump in directory format
- dump was taken of only one particular database

Not-so-nice solutions coming to mind:

- rely on the dump file name
- use pg_restore to create an SQL dump
  with --create and grep the SQL file
  for "create database ..."
- restore and compare psql -l output
  before/after the fact

However, I'd wish for a less fragile solution, letting me
learn the database directly from the directory dump (given
the above assumptions about the dump).

Am I missing options for doing so ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sat, Mar 12, 2016 at 09:33:33PM +0100, Karsten Hilbert wrote:

> Not-so-nice solutions coming to mind:
>
> - rely on the dump file name
> - use pg_restore to create an SQL dump
>   with --create and grep the SQL file
>   for "create database ..."
> - restore and compare psql -l output
>   before/after the fact

I _have_ tried

    pg_filedump toc.dat

to no avail.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sat, Mar 12, 2016 at 09:38:13PM +0100, Karsten Hilbert wrote:

> > Not-so-nice solutions coming to mind:
> >
> > - rely on the dump file name
> > - use pg_restore to create an SQL dump
> >   with --create and grep the SQL file
> >   for "create database ..."
> > - restore and compare psql -l output
> >   before/after the fact

Another option that comes to mind is

    pg_restore -l $DUMPDIR | grep dbname: | cut -f 7 -d ' ' -s

but that is quite fragile on the

    -f 7 -d ' '

side of things but that's another question.


Start of pg_restore -l output:

    ;
    ; Archive created at 2016-03-07 21:15:06 CET
    ;     dbname: gnumed_v20
    ;     TOC Entries: 5187
    ;     Compression: 0
    ;     Dump Version: 1.12-0
    ;     Format: DIRECTORY
    ;     Integer: 4 bytes
    ;     Offset: 8 bytes
    ;     Dumped from database version: 9.5.1
    ;     Dumped by pg_dump version: 9.5.1
    ;
    ;
    ; Selected TOC Entries:
    ;
    8525; 1262 181294 DATABASE - gnumed_v20 gm-dbo


Any better suggestions ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sat, Mar 12, 2016 at 03:55:32PM -0500, Melvin Davidson wrote:

> hmmm, let's see. You haven't specified PostgreSQL version or O/S as is
> common sense and courtesy, so I will choose one for you.

:-)  Sorry.   I am on 9.5.1 on Debian 8.0.

OTOH, in the wild it could be any OS and PG 9.1.0 upwards.

And thanks for investing time anyway !

> You are using PostgreSQL version 8.4 on Ubuntu 14.04
> Since pg_dump requires an output file, and the database you are dumping
> must be known, just just the db name in the path.
> eg: pg_dump unknown_db > /dumpdir/unknown_db/whatever_filename_you_want.dmp

Thanks for this suggestion. This option was already
included in the ones I figured out myself :-)

However, I had listed the assumptions about the dump and the
db name being in the dump name wasn't one of them   8-)

Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On 3/12/2016 12:33 PM, Karsten Hilbert wrote:
> How can I (programmatically) find out which database a dump
> was taken from given the dump file ?
>
> Constraints of the question:
>
> - existing dump in directory format
> - dump was taken of only one particular database

I know of no documentation on the format of the toc.dat file contained
in that directory format pg_dump output (short of reading the source to
pg_dump/restore?) but I tried a hexdump...

$ hexdump -C junky/toc.dat
00000000  50 47 44 4d 50 01 0c 00  04 08 03 01 01 00 00 00
|PGDMP...........|
00000010  00 24 00 00 00 00 2d 00  00 00 00 0c 00 00 00 00
|.$....-.........|
00000020  0c 00 00 00 00 02 00 00  00 00 74 00 00 00 00 00
|..........t.....|
00000030  00 00 00 00 04 00 00 00  6a 75 6e 6b 00 06 00 00
|........junk....|
00000040  00 39 2e 33 2e 31 31 00  06 00 00 00 39 2e 33 2e
|.9.3.11.....9.3.|
(tons more deleted)

and note that 'junk' is in fact the name of the database.   But I doubt
the format of this toc.dat file is guaranteed to be immutable



--
john r pierce, recycling bits in santa cruz

On Sat, Mar 12, 2016 at 10:05:47PM +0100, Karsten Hilbert wrote:

> :-)  Sorry.   I am on 9.5.1 on Debian 8.0.

Debian Testing to be precise:

    root@hermes:~/tmp# apt-cache policy postgresql
    postgresql:
      Installiert:           9.5+172
      Installationskandidat: 9.5+172
      Versionstabelle:
     *** 9.5+172 0
            990 ftp://ftp.de.debian.org/debian/ stretch/main i386 Packages
            500 ftp://ftp.de.debian.org/debian/ unstable/main i386 Packages
            100 /var/lib/dpkg/status
         9.4+165 0
            500 http://ftp.de.debian.org/debian/ jessie/main i386 Packages
    root@hermes:~/tmp# su - postgres
    postgres@hermes:~$ psql
    Ausgabeformat ist „wrapped“.
    psql (9.5.1)
    Geben Sie „help“ für Hilfe ein.



    Linux hermes 4.4.0-1-686-pae #1 SMP Debian 4.4.4-2 (2016-03-09) i686 GNU/Linux


in case it should matter.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sat, Mar 12, 2016 at 01:12:52PM -0800, John R Pierce wrote:

>> Constraints of the question:
>>
>> - existing dump in directory format
>> - dump was taken of only one particular database
>
> I know of no documentation on the format of the toc.dat file contained in
> that directory format pg_dump output (short of reading the source to
> pg_dump/restore?) but I tried a hexdump...
>
> $ hexdump -C junky/toc.dat
> 00000000  50 47 44 4d 50 01 0c 00  04 08 03 01 01 00 00 00
> |PGDMP...........|
> 00000010  00 24 00 00 00 00 2d 00  00 00 00 0c 00 00 00 00
> |.$....-.........|
> 00000020  0c 00 00 00 00 02 00 00  00 00 74 00 00 00 00 00
> |..........t.....|
> 00000030  00 00 00 00 04 00 00 00  6a 75 6e 6b 00 06 00 00
> |........junk....|
> 00000040  00 39 2e 33 2e 31 31 00  06 00 00 00 39 2e 33 2e
> |.9.3.11.....9.3.|
> (tons more deleted)
>
> and note that 'junk' is in fact the name of the database.   But I doubt the
> format of this toc.dat file is guaranteed to be immutable

I looked at that, too, but was quite worried that this
solution would be very fragile.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sat, Mar 12, 2016 at 04:17:07PM -0500, Melvin Davidson wrote:

> BTW, other than the obvious of including the name in path or file, if you
> are referring to previous/existing dumps

I do.

> grep -i some_dump_file 'CREATE DATABASE'

That will not work (directly) because the dump is in
directory format.

> If nothing is found, then the dump can be applied to ANY database.

Since one can create an SQL dump from the directory dump the
above becomes possible by appropriate use of

    pg_restore -C ... | grep CREATE DATABASE

This is, indeed, the option (after pg_restore -l | grep ...)
I consider least fragile.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On 3/12/2016 12:59 PM, Karsten Hilbert wrote:
> Another option that comes to mind is
>
>     pg_restore -l $DUMPDIR | grep dbname: | cut -f 7 -d ' ' -s
>
> but that is quite fragile on the
>
>     -f 7 -d ' '
>
> side of things but that's another question.

starting with...

  pg_dump -Fd -f junky "databasename"

I think...

  pg_restore -l junky | awk  '/^;\wdbname: (.*)/{print $3}'

is more robust.   but it fails on a database name with an embedded space
(yes, these are valid).

pg_restore -l junky | grep 'dbname:'  | head -1 | sed 's/^; *dbname: //'

should work even for dbnames with embedded spaces.

the head -1 is just in case there the data 'dbname:' occurs in the
database somewhere.


--
john r pierce, recycling bits in santa cruz

On 3/12/2016 1:40 PM, John R Pierce wrote:
> pg_restore -l junky | awk  '/^;\wdbname: (.*)/{print $3}'

oops, pasted the wrong one, meant that one to be ...

     pg_restore -l junky | awk  '/^; +dbname: /{print $3}'


but I think the 2nd one is more robust

--
john r pierce, recycling bits in santa cruz

On Sat, Mar 12, 2016 at 02:59:05PM -0700, David G. Johnston wrote:

> And a much more reasonable assumption would have been 9.5 - let the user
> complain if/when the advice doesn't work because they are on an unstated
> older release that doesn't support the feature in question.
>
> I guess the O/S would be needed for syntax purposes but the typical
> responder would simply provide an answer if whatever O/S shell they are
> familiar with and deal with the issue of making it work elsewhere if needed.
>
> It is, however, quite needed to report the version (and usually O/S) when
> posting to the -bugs list.  This here is the -general list and the need to
> do so is generally not all that frequent though is appreciated as it makes
> giving targeted advice a bit easier.

Thanks but no worry. I am myself actually in favor of
providing sufficient information. So I did and took the
possibly (!) ever so slight chiding undertones (?) as a
lesson in humility :-))

Eventually, I went with

    TARGET_DB=`pg_restore -C -s ${BACKUP}.dir | head -n 40 | grep -i "create database gnumed_v" | cut -f 3 -d " "`

which is intended to be used under

    bash:
      Installiert:           4.3-14+b1
      Installationskandidat: 4.3-14+b1
      Versionstabelle:
     *** 4.3-14+b1 0
            990 ftp://ftp.de.debian.org/debian/ stretch/main i386 Packages
            500 ftp://ftp.de.debian.org/debian/ unstable/main i386 Packages
            100 /var/lib/dpkg/status
         4.3-11+b1 0
            500 http://ftp.de.debian.org/debian/ jessie/main i386 Packages

Note that I changed the "head -1" to "head -n 40"

    coreutils:
      Installiert:           8.25-2
      Installationskandidat: 8.25-2
      Versionstabelle:
     *** 8.25-2 0
            990 ftp://ftp.de.debian.org/debian/ stretch/main i386 Packages
            500 ftp://ftp.de.debian.org/debian/ unstable/main i386 Packages
            100 /var/lib/dpkg/status
         8.23-4 0
            500 http://ftp.de.debian.org/debian/ jessie/main i386 Packages

Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Hello,

since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian 8.3. Everything is fine except the daily backup.
Whencalling pg_dump as part of a cron job pg_dump crashes: 

2016-03-15 01:00:02 CETFATAL:  semctl(23232524, 3, SETVAL, 0) failed: Invalid argument
2016-03-15 01:00:02 CETLOG:  server process (PID 22279) exited with exit code 1
2016-03-15 01:00:02 CETLOG:  terminating any other active server processes
2016-03-15 01:00:02 CETWARNING:  terminating connection because of crash of another server process
2016-03-15 01:00:02 CETDETAIL:  The postmaster has commanded this server process to roll back the current transaction
andexit, because another server process exited abnormally and possibly corrupted shared memory. 
2016-03-15 01:00:02 CETHINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-03-15 01:00:02 CETLOG:  all server processes terminated; reinitializing
2016-03-15 01:00:02 CETLOG:  could not remove shared memory segment "/PostgreSQL.1804289383": No such file or directory
2016-03-15 01:00:02 CETLOG:  semctl(22839296, 0, IPC_RMID, ...) failed: Invalid argument
2016-03-15 01:00:02 CETLOG:  semctl(22872065, 0, IPC_RMID, ...) failed: Invalid argument
2016-03-15 01:00:02 CETLOG:  semctl(22904834, 0, IPC_RMID, ...) failed: Invalid argument
…

I am calling pg_dump in my cronjob like this:
su - mmppostgres -c "/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 > /my_backup_path/mydatabase_1_0_0.dump"

After the crash the database runs in recovery mode. A restart of the database brings everything back to normal.
This crash is always reproducible and occurs every night during backup. When calling the same command via the command
lineeverything run fine. In the system log I can see: 

Mar 15 01:00:01 mymachine systemd[1]: Started User Manager for UID 1001.
Mar 15 01:00:02 mymachine systemd[1]: Stopping User Manager for UID 1001...
Mar 15 01:00:02 mymachine systemd[22241]: Stopping Default.
Mar 15 01:00:02 mymachine systemd[22241]: Stopped target Default.
Mar 15 01:00:02 mymachine systemd[22241]: Stopping Basic System.
Mar 15 01:00:02 mymachine systemd[22241]: Stopped target Basic System.
Mar 15 01:00:02 mymachine systemd[22241]: Stopping Paths.
Mar 15 01:00:02 mymachine systemd[22241]: Stopped target Paths.
Mar 15 01:00:02 mymachine systemd[22241]: Stopping Timers.
Mar 15 01:00:02 mymachine systemd[22241]: Stopped target Timers.
Mar 15 01:00:02 mymachine systemd[22241]: Stopping Sockets.
Mar 15 01:00:02 mymachine systemd[22241]: Stopped target Sockets.
Mar 15 01:00:02 mymachine systemd[22241]: Starting Shutdown.
Mar 15 01:00:02 mymachine systemd[22241]: Reached target Shutdown.
Mar 15 01:00:02 mymachine systemd[22241]: Starting Exit the Session...
Mar 15 01:00:02 mymachine systemd[22241]: Received SIGRTMIN+24 from PID 22249 (kill).

While searching through mailing lists I tried to exclude the following possibilities, which might cause the error:

1st possible solution: Another PostgreSQL instance running … no.
Some people get the ‘semctl’ error when running a second PostgreSQL process using the same semaphores. I can definitely
excludethat. I have no other PostgreSQL instance running on the machine. 

2nd possible solution: Checking with 'ipcs -s’ for another process deleting semaphores.
Before a crash my output looks like this:

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x0052e6a9 20742144   mmppostgre 600        17
0x0052e6aa 20774913   mmppostgre 600        17
0x0052e6ab 20807682   mmppostgre 600        17
0x0052e6ac 20840451   mmppostgre 600        17
0x0052e6ad 20873220   mmppostgre 600        17
0x0052e6ae 20905989   mmppostgre 600        17
0x0052e6af 20938758   mmppostgre 600        17
0x0052e6b0 20971527   mmppostgre 600        17
0x0052e6b1 21004296   mmppostgre 600        17
0x0052e6b2 21037065   mmppostgre 600        17
0x0052e6b3 21069834   mmppostgre 600        17
0x0052e6b4 21102603   mmppostgre 600        17
0x0052e6b5 21135372   mmppostgre 600        17
0x0052e6b6 21168141   mmppostgre 600        17
0x00000000 21266446   www-data   600        1
0x00000000 21299215   www-data   600        1

After the crash the output looks like that:
------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x0052e6a9 22380544   mmppostgre 600        17
0x0052e6aa 22413313   mmppostgre 600        17
0x0052e6ab 22446082   mmppostgre 600        17
0x0052e6ac 22478851   mmppostgre 600        17
0x0052e6ad 22511620   mmppostgre 600        17
0x0052e6ae 22544389   mmppostgre 600        17
0x0052e6af 22577158   mmppostgre 600        17
0x0052e6b0 22609927   mmppostgre 600        17
0x0052e6b1 22642696   mmppostgre 600        17
0x0052e6b2 22675465   mmppostgre 600        17
0x0052e6b3 22708234   mmppostgre 600        17
0x0052e6b4 22741003   mmppostgre 600        17
0x0052e6b5 22773772   mmppostgre 600        17
0x0052e6b6 22806541   mmppostgre 600        17
0x00000000 21856270   www-data   600        1
0x00000000 21889039   www-data   600        1

As all server processes have been cancelled it seems to me normal, that the semids have been changed. Beyond that I can
notsee anything which might be useful for me. 

3rd possible solution: Strange values for shared memory:
My Debian 8.3 default settings for shared memory have been:

kernel.shmall = 18446744073692774399
kernel.shmmax = 18446744073692774399
kernel.shmmni = 4096

These values have been looking strange to me, so I changed them to some more realistic values:
kernel.shmall = 4194304
kernel.shmmax = 17179869184
kernel.shmmni = 4096

4th  possible solution: I have read here: https://lists.freedesktop.org/archives/systemd-devel/2014-April/018373.html
that logging into the postgres user in and out might delete semaphore memory, but I cannot reproduce this.

Interesting enough I have another PostgreSQL version running perfectly under Debian 8.3 on a different machine. This is
aPostgreSQL 9.4.1. It uses the same backup mechanism. I hate to consider a downgrade as a possible solution. May be an
info,which might be useful: my machine has 64 GB RAM. 

Any ideas where to continue my search?

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75
http://www.mmp.lu

On Tue, Mar 15, 2016 at 4:10 PM, Matthias Schmitt
<matthias.schmitt@mmp.lu> wrote:
> since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian 8.3. Everything is fine except the daily backup.
Whencalling pg_dump as part of a cron job pg_dump crashes: 
>
> 2016-03-15 01:00:02 CETFATAL:  semctl(23232524, 3, SETVAL, 0) failed: Invalid argument
> 2016-03-15 01:00:02 CETLOG:  server process (PID 22279) exited with exit code 1
> [...]
> 4th  possible solution: I have read here: https://lists.freedesktop.org/archives/systemd-devel/2014-April/018373.html
> that logging into the postgres user in and out might delete semaphore memory, but I cannot reproduce this.

That's scary, and an entrance for many crashes... Perhaps there are
some underlying calls of ipcrm?

> Interesting enough I have another PostgreSQL version running perfectly under Debian 8.3 on a different machine. This
isa PostgreSQL 9.4.1. It uses the same backup mechanism. I hate to consider a downgrade as a possible solution. May be
aninfo, which might be useful: my machine has 64 GB RAM. 
>
> Any ideas where to continue my search?

Hm, and both of them are managed with systemd? Are there differences
in each one's spec file, I find rather bad the fact that there is
something removing the semaphores at OS level all-of-a-sudden.
--
Michael

On Tue, 2016-03-15 at 16:10 +0100, Matthias Schmitt wrote:
> Hello,
>
> since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian
> 8.3. Everything is fine except the daily backup. When calling pg_dump
> as part of a cron job pg_dump crashes:
>
> 2016-03-15 01:00:02 CETFATAL:  semctl(23232524, 3, SETVAL, 0) failed:
> Invalid argument
> 2016-03-15 01:00:02 CETLOG:  server process (PID 22279) exited with
> exit code 1
> 2016-03-15 01:00:02 CETLOG:  terminating any other active server
> processes
> 2016-03-15 01:00:02 CETWARNING:  terminating connection because of
> crash of another server process
> 2016-03-15 01:00:02 CETDETAIL:  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.
> 2016-03-15 01:00:02 CETHINT:  In a moment you should be able to
> reconnect to the database and repeat your command.
> 2016-03-15 01:00:02 CETLOG:  all server processes terminated;
> reinitializing
> 2016-03-15 01:00:02 CETLOG:  could not remove shared memory segment
> "/PostgreSQL.1804289383": No such file or directory
> 2016-03-15 01:00:02 CETLOG:  semctl(22839296, 0, IPC_RMID, ...)
> failed: Invalid argument
> 2016-03-15 01:00:02 CETLOG:  semctl(22872065, 0, IPC_RMID, ...)
> failed: Invalid argument
> 2016-03-15 01:00:02 CETLOG:  semctl(22904834, 0, IPC_RMID, ...)
> failed: Invalid argument
> …
>
> I am calling pg_dump in my cronjob like this:
> su - mmppostgres -c "/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 >
> /my_backup_path/mydatabase_1_0_0.dump"
>

Hello Matthias,


I'm running Debian 4.3.5-1 (2016-02-06) x86_64 GNU/Linux.

Backups are done via a shell script using double hyphen syntax, as in
e.g.:-

pg_dump --port=5433 --dbname=mydatabase_1_0_0 --username=mmppostgres
 --file=/my_backup_path/mydatabase_1_0_0.dump

We do it twice. First with --data_only and the second time with
 --format=custom

Hasn't failed yet. Don't know if this helps at all but worth a try.

Cheers,
Rob

On 03/15/2016 08:10 AM, Matthias Schmitt wrote:
> Hello,
>
> since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian 8.3. Everything is fine except the daily backup.
Whencalling pg_dump as part of a cron job pg_dump crashes: 
>
> 2016-03-15 01:00:02 CETFATAL:  semctl(23232524, 3, SETVAL, 0) failed: Invalid argument
> 2016-03-15 01:00:02 CETLOG:  server process (PID 22279) exited with exit code 1
> 2016-03-15 01:00:02 CETLOG:  terminating any other active server processes
> 2016-03-15 01:00:02 CETWARNING:  terminating connection because of crash of another server process
> 2016-03-15 01:00:02 CETDETAIL:  The postmaster has commanded this server process to roll back the current transaction
andexit, because another server process exited abnormally and possibly corrupted shared memory. 
> 2016-03-15 01:00:02 CETHINT:  In a moment you should be able to reconnect to the database and repeat your command.
> 2016-03-15 01:00:02 CETLOG:  all server processes terminated; reinitializing
> 2016-03-15 01:00:02 CETLOG:  could not remove shared memory segment "/PostgreSQL.1804289383": No such file or
directory
> 2016-03-15 01:00:02 CETLOG:  semctl(22839296, 0, IPC_RMID, ...) failed: Invalid argument
> 2016-03-15 01:00:02 CETLOG:  semctl(22872065, 0, IPC_RMID, ...) failed: Invalid argument
> 2016-03-15 01:00:02 CETLOG:  semctl(22904834, 0, IPC_RMID, ...) failed: Invalid argument
> …
>
> I am calling pg_dump in my cronjob like this:
> su - mmppostgres -c "/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 > /my_backup_path/mydatabase_1_0_0.dump"
>
> After the crash the database runs in recovery mode. A restart of the database brings everything back to normal.
> This crash is always reproducible and occurs every night during backup. When calling the same command via the command
lineeverything run fine. In the system log I can see: 
>

Is the command you run via the command line exactly the same, including
the su -?

What user are you running the cronjob as?

How do you supply the password for the mmppostgres user?

> Any ideas where to continue my search?
>
> Best regards
>
> Matthias Schmitt
>
> magic moving pixel s.a.
> 23, Avenue Grande-Duchesse Charlotte
> L-3441 Dudelange
> Luxembourg
> Phone: +352 54 75 75
> http://www.mmp.lu
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hello,

sorry for the late response.

> On 15 Mar 2016, at 18:59, rob stone <floriparob@gmail.com> wrote:
>
> I'm running Debian 4.3.5-1 (2016-02-06) x86_64 GNU/Linux.
>
> Backups are done via a shell script using double hyphen syntax, as in
> e.g.:-
>
> pg_dump --port=5433 --dbname=mydatabase_1_0_0 --username=mmppostgres
>  --file=/my_backup_path/mydatabase_1_0_0.dump
>
> We do it twice. First with --data_only and the second time with
>  --format=custom
>
> Hasn't failed yet. Don't know if this helps at all but worth a try.

Thank you for your answer. But sorry, no, this does not change anything. Same server crash when executed in a cron job.
Itruns perfectly when executed manually from the shell. 

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75
http://www.mmp.lu

Hello,

> On 16 Mar 2016, at 14:55, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 03/15/2016 08:10 AM, Matthias Schmitt wrote:
>> Hello,
>>
>> since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian 8.3. Everything is fine except the daily
backup.When calling pg_dump as part of a cron job pg_dump crashes: 
>>
>> 2016-03-15 01:00:02 CETFATAL:  semctl(23232524, 3, SETVAL, 0) failed: Invalid argument
>> 2016-03-15 01:00:02 CETLOG:  server process (PID 22279) exited with exit code 1
>> 2016-03-15 01:00:02 CETLOG:  terminating any other active server processes
>> 2016-03-15 01:00:02 CETWARNING:  terminating connection because of crash of another server process
>> 2016-03-15 01:00:02 CETDETAIL:  The postmaster has commanded this server process to roll back the current
transactionand exit, because another server process exited abnormally and possibly corrupted shared memory. 
>> 2016-03-15 01:00:02 CETHINT:  In a moment you should be able to reconnect to the database and repeat your command.
>> 2016-03-15 01:00:02 CETLOG:  all server processes terminated; reinitializing
>> 2016-03-15 01:00:02 CETLOG:  could not remove shared memory segment "/PostgreSQL.1804289383": No such file or
directory
>> 2016-03-15 01:00:02 CETLOG:  semctl(22839296, 0, IPC_RMID, ...) failed: Invalid argument
>> 2016-03-15 01:00:02 CETLOG:  semctl(22872065, 0, IPC_RMID, ...) failed: Invalid argument
>> 2016-03-15 01:00:02 CETLOG:  semctl(22904834, 0, IPC_RMID, ...) failed: Invalid argument
>> …
>>
>> I am calling pg_dump in my cronjob like this:
>> su - mmppostgres -c "/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 > /my_backup_path/mydatabase_1_0_0.dump"
>>
>> After the crash the database runs in recovery mode. A restart of the database brings everything back to normal.
>> This crash is always reproducible and occurs every night during backup. When calling the same command via the
commandline everything run fine. In the system log I can see: 
>>
>
> Is the command you run via the command line exactly the same, including the su -?

Yes.

> What user are you running the cronjob as?

root

> How do you supply the password for the mmppostgres user?

I configured in pg_hba.conf:

local  all  mmppostgres  trust

All local connections from this user are trusted.

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75
http://www.mmp.lu

On 03/20/2016 08:24 AM, Matthias Schmitt wrote:
> Hello,
>
>> On 16 Mar 2016, at 14:55, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 03/15/2016 08:10 AM, Matthias Schmitt wrote:
>>> Hello,
>>>
>>> since two weeks I am trying to get PostgreSQL 9.5.1 to run on Debian 8.3. Everything is fine except the daily
backup.When calling pg_dump as part of a cron job pg_dump crashes: 
>>>
>>> 2016-03-15 01:00:02 CETFATAL:  semctl(23232524, 3, SETVAL, 0) failed: Invalid argument
>>> 2016-03-15 01:00:02 CETLOG:  server process (PID 22279) exited with exit code 1
>>> 2016-03-15 01:00:02 CETLOG:  terminating any other active server processes
>>> 2016-03-15 01:00:02 CETWARNING:  terminating connection because of crash of another server process
>>> 2016-03-15 01:00:02 CETDETAIL:  The postmaster has commanded this server process to roll back the current
transactionand exit, because another server process exited abnormally and possibly corrupted shared memory. 
>>> 2016-03-15 01:00:02 CETHINT:  In a moment you should be able to reconnect to the database and repeat your command.
>>> 2016-03-15 01:00:02 CETLOG:  all server processes terminated; reinitializing
>>> 2016-03-15 01:00:02 CETLOG:  could not remove shared memory segment "/PostgreSQL.1804289383": No such file or
directory
>>> 2016-03-15 01:00:02 CETLOG:  semctl(22839296, 0, IPC_RMID, ...) failed: Invalid argument
>>> 2016-03-15 01:00:02 CETLOG:  semctl(22872065, 0, IPC_RMID, ...) failed: Invalid argument
>>> 2016-03-15 01:00:02 CETLOG:  semctl(22904834, 0, IPC_RMID, ...) failed: Invalid argument
>>> …
>>>
>>> I am calling pg_dump in my cronjob like this:
>>> su - mmppostgres -c "/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 > /my_backup_path/mydatabase_1_0_0.dump"
>>>
>>> After the crash the database runs in recovery mode. A restart of the database brings everything back to normal.
>>> This crash is always reproducible and occurs every night during backup. When calling the same command via the
commandline everything run fine. In the system log I can see: 
>>>
>>
>> Is the command you run via the command line exactly the same, including the su -?
>
> Yes.
>
>> What user are you running the cronjob as?
>
> root
>
>> How do you supply the password for the mmppostgres user?
>
> I configured in pg_hba.conf:
>
> local  all  mmppostgres  trust
>
> All local connections from this user are trusted.

So what happens if you either?:

1) In the root crontab, change the command to:

/Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 -U mmpostgres > /my_backup_path/mydatabase_1_0_0.dump

2) Run the command in 1) in the mmppostgres crontab

>
> Best regards
>
> Matthias Schmitt
>
> magic moving pixel s.a.
> 23, Avenue Grande-Duchesse Charlotte
> L-3441 Dudelange
> Luxembourg
> Phone: +352 54 75 75
> http://www.mmp.lu
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Mar 21, 2016 at 4:18 AM, Matthias Schmitt
<matthias.schmitt@mmp.lu> wrote:
> Hello,
>
> sorry for the late response.
>
>> On 15 Mar 2016, at 18:59, rob stone <floriparob@gmail.com> wrote:
>>
>> I'm running Debian 4.3.5-1 (2016-02-06) x86_64 GNU/Linux.
>>
>> Backups are done via a shell script using double hyphen syntax, as in
>> e.g.:-
>>
>> pg_dump --port=5433 --dbname=mydatabase_1_0_0 --username=mmppostgres
>>  --file=/my_backup_path/mydatabase_1_0_0.dump
>>
>> We do it twice. First with --data_only and the second time with
>>  --format=custom
>>
>> Hasn't failed yet. Don't know if this helps at all but worth a try.
>
> Thank you for your answer. But sorry, no, this does not change anything. Same server crash when executed in a cron
job.It runs perfectly when executed manually from the shell. 

Is this related?

http://www.postgresql.org/message-id/CAK7tEys9-O4BTERbs3Xuk2BfFNNd55u2sM9j5R2Fi7v6BHjrQw@mail.gmail.com

--
Thomas Munro
http://www.enterprisedb.com

Hello,

thank you for your answers.

> On 20 Mar 2016, at 16:56, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> So what happens if you either?:
>
> 1) In the root crontab, change the command to:
>
> /Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 -U mmpostgres > /my_backup_path/mydatabase_1_0_0.dump

This will make no difference.

> On 21 Mar 2016, at 03:42, Thomas Munro <thomas.munro@enterprisedb.com> wrote:
>
> Is this related?
>
> http://www.postgresql.org/message-id/CAK7tEys9-O4BTERbs3Xuk2BfFNNd55u2sM9j5R2Fi7v6BHjrQw@mail.gmail.com

Yes, this is related. I tried to set RemoveIPC=no, but it made no difference.

Finally I found a solution with the hint given here:
http://www.postgresql.org/message-id/56A52018.1030001@gmx.net

Systemd defaults to remove all IPC (including SYSV memory) when a user "fully" logs out. This seems to be happen when
runningthe cron job. It seems to be a difference if the job is running as a “normal” user or a “system” user. 

So I modified my existing postgres user to be a system user

usermod -u <id below 1000> mmppostgres

an now my backup is running. I cannot remember finding such a hint in the PostgreSQL documentation. It would be worth
mentioningit. 

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75
http://www.mmp.lu