Q: extract database name from directory dump

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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

Then you can get the db name by lookiing at the directory you dumped to! At
least that is what most dba's would do.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

BTW, other than the obvious of including the name in path or file, if you
are referring to previous/existing dumps than one of two options apply.

grep -i some_dump_file 'CREATE DATABASE'
If it's found, then you know it's from at least one known database.
If nothing is found, then the dump can be applied to ANY database.

On Sat, Mar 12, 2016 at 4:12 PM, John R Pierce <pierce@hogranch.com> wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Mar 12, 2016 at 2:05 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

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 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.

David J.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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"

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 line everything 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 exclude that. 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 not see 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 a PostgreSQL 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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. 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
[...]
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 is a PostgreSQL 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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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"

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 line 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 -?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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"

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 line 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general