Start Free Trial

asked on

Postgres manual db_dumpall fails

Postgres manual DB dump fails. I receive the error
server version: 9.2.1; pg_dumpall version: 9.1.4
aborting because of server version mismatch.

I have a script that runs at night in tandem with my SuperDuper! backup software. The script stops the web services on my MacMini OSX server, then runs a db dumpall and copies that dump to a directory where then the SuperDuper includes it in the nightly backup. This script has been running rock solid for months. After the latest mac osx update however now this script errors out. And I receive the above error. I can no longer even run a dumpall from the command line I receive a permission denied response. I have contacted apple support, and they were unhelpful. Stating that they had nothing for me.

| 12:05:30 AM | Info | SuperDuper!, 2.7.1 (91), path: /Applications/SuperDuper!.app, Mac OS 10.8.2 build 12C60 (i386)

| 12:05:30 AM | Info | Started on Wed, Feb 6, 2013 at 12:05 AM

| 12:05:30 AM | Info | Source Volume: Server HD, mount: /, device: /dev/disk0s2, media: APPLE HDD HTS727550A9E362, interconnect: Internal SATA, file system: "Case-sensitive Journaled HFS+", OS: 10.8.2 (12C60), capacity: 499.25 GB, used: 118.52 GB, directories: 76753, files: 324858, ejectable: NO, ACLs: Enabled

| 12:05:30 AM | Info | Target Volume: Seagate, mount: /Volumes/Seagate, device: /dev/disk2s2, media: Seagate Backup+ Desk, interconnect: External USB, file system: "Case-sensitive Journaled HFS+", OS: 10.8.2 (12C60), capacity: 3000.25 GB, used: 117.88 GB, directories: 78629, files: 393107, ejectable: YES, ACLs: Enabled

| 12:05:30 AM | Info | Copy Mode : Copy Newer files

| 12:05:30 AM | Info | Copy Script : Backup - all files.dset

| 12:05:30 AM | Info | Transcript : BuildTranscript.plist

| 12:05:31 AM | Info | PHASE: 1. Prepare to Copy Files

| 12:05:31 AM | Info | ...ACTION: Preparing Server HD

| 12:05:31 AM | Info | ......COMMAND => Verifying the integrity of volinfo.database

| 12:05:31 AM | Info | volinfo.database OK

| 12:05:31 AM | Info | ......COMMAND => Enabling permissions on Server HD

| 12:05:32 AM | Info | File system user/group ownership enabled

| 12:05:32 AM | Info | ......COMMAND => Verifying that permissions are enabled for Server HD

| 12:05:32 AM | Info | Permissions on '/' are enabled.

| 12:05:32 AM | Info | ...ACTION: Preparing Seagate

| 12:05:32 AM | Info | ......COMMAND => Enabling permissions on Seagate

| 12:05:32 AM | Info | /dev/disk0s2 on / (hfs, local, journaled)

| 12:05:32 AM | Info | devfs on /dev (devfs, local, nobrowse)

| 12:05:32 AM | Info | map -hosts on /net (autofs, nosuid, automounted, nobrowse)

| 12:05:32 AM | Info | map auto_home on /home (autofs, automounted, nobrowse)

| 12:05:32 AM | Info | /dev/disk1s2 on /Volumes/Macintosh HD2 (hfs, local, journaled)

| 12:05:32 AM | Info | /dev/disk2s2 on /Volumes/Seagate (hfs, local, journaled)

| 12:05:32 AM | Info | /dev/disk3s2 on /Volumes/Time Machine Backups (hfs, local, journaled)

| 12:05:32 AM | Info | map -fstab on /Network/Servers (autofs, automounted, nobrowse)

| 12:05:32 AM | Info | ......COMMAND => Verifying that permissions are enabled for Seagate

| 12:05:32 AM | Info | Permissions on '/Volumes/Seagate' are enabled.

| 12:05:32 AM | Info | ......COMMAND => Verifying that Seagate ACL support matches Server HD

| 12:05:32 AM | Info | ...ACTION: Preserving Spotlight state on Seagate

| 12:05:32 AM | Info | ......COMMAND => Disabling Spotlight search indexing on Seagate

| 12:05:32 AM | Info | ...ACTION: Running shell script backup.sh

| 12:05:32 AM | Info | ......COMMAND => Invoking Before Copy shell script: /System/backup.sh

12:05:32 AM | Info | addressbook:state = "STOPPED"

| 12:05:32 AM | Info | addressbook:setStateVersion = 1

| 12:05:32 AM | Info | addressbook:readWriteSettingsVersion = 1

| 12:05:32 AM | Info | calendar:state = "STOPPED"

| 12:05:32 AM | Info | calendar:setStateVersion = 1

| 12:05:32 AM | Info | calendar:readWriteSettingsVersion = 1

| 12:05:54 AM | Info | wiki:state = "STOPPED"

| 12:05:54 AM | Info | devicemgr:state = "STOPPED"

| 12:05:55 AM | Info | web:state = "STOPPED"

| 12:05:55 AM | Info | web:status = 0

| 12:05:55 AM | Info | postgres:state = "RUNNING"

| 12:05:55 AM | Error | server version: 9.2.1; pg_dumpall version: 9.1.4

| 12:05:55 AM | Error | aborting because of server version mismatch

*****

/Applications/Server.app/Contents/ServerRoot/usr/sbin/serveradmin start postgres

SA6=$!

/usr/bin/wait $SA6

PGUSER=_postgres pg_dumpall >/Odbackup/cluster`date +\%Y\%m\%d`.dump

PS1=$!

******

dsync postmaster

berkeley:bin admin$ sudo pg_dumpall >/Odbackup/cluster`date +\%Y\%m\%d`.dump

-bash: /Odbackup/cluster20130206.dump: Permission denied

berkeley:bin admin$ PGUSER=_postgres pg_dumpall >/Odbackup/cluster`date +\%Y\%m\%d`.dump

-bash: /Odbackup/cluster20130206.dump: Permission denied

berkeley:bin admin$ pwd

/Applications/Server.app/Contents/ServerRoot/usr/bin

berkeley:bin admin$

It looks like there's two errors: first, it can't create the backup file in "/Odbackup/", and secondly that the server's version is higher than pg_dump's version. I would imagine the server got upgraded, but for whatever reason the binaries from the old version were left about.

I'd suggest searching for all locations of "pg_dump", there should be one in a different folder matching the server's version. In Linux, you can either use locate (you can use find as well):

sudo updatedb && locate pg_dump | grep bin

Open in new window

ASKER

*******:~ admin$ sudo updatedb && locate pg_dump | grep bin
Password:
sudo: updatedb: command not found
*******:~ admin$

berkeley:~ admin$ locate pg_dumpall
locate: locate database header corrupt, bigram char outside 0, 32-127: -1

Try this:

find /usr -name pg_dump

Open in new window

If you can't find any that way, change "/usr" to just "/".

For every result, check the version. Like this:

user@server:~$ find /usr -name pg_dump
/usr/lib/postgresql/9.1/bin/pg_dump
/usr/bin/pg_dump
user@server:~$ /usr/lib/postgresql/9.1/bin/pg_dump --version
pg_dump (PostgreSQL) 9.1.7
user@server:~$ /usr/bin/pg_dump --version
pg_dump (PostgreSQL) 9.1.7

Open in new window

ASKER

berkeley:~ admin$ sudo find /usr -name pg_dump
Password:
/usr/bin/pg_dump
berkeley:~ admin$ /usr/bin/pg_dump --version
pg_dump (PostgreSQL) 9.1.4

ASKER

berkeley:~ admin$ find /usr -name pg_dump
/usr/bin/pg_dump

The find returns only the one instance

ASKER

Password:
pg_dumpall: could not connect to database "template1": FATAL: role "root" does not exist

berkeley:bin admin$ /usr/bin/pg_dumpall >/tmp/cluster`date +\%Y\%m\%d`.dump
pg_dumpall: could not connect to database "template1": could not connect to server: Permission denied
      Is the server running locally and accepting
      connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

berkeley:bin admin$ PGUSER=_postgres /usr/bin/pg_dumpall >/tmp/cluster`date +\%Y\%m\%d`.dump
pg_dumpall: could not connect to database "template1": could not connect to server: Permission denied
      Is the server running locally and accepting
      connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

berkeley:bin admin$

Usually you have to be the postgres user, or configure the system so that local users can say they're postgres:

/usr/bin/pg_dumpall -U postgres > /tmp/cluster`date +\%Y\%m\%d`.dump

Open in new window

ASKER

berkeley:bin admin$ /usr/bin/pg_dumpall -U postgres > /tmp/cluster`date +\%Y\%m\%d`.dump
pg_dumpall: could not connect to database "template1": could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

berkeley:bin admin$

ASKER

I ran into something like this before on a different macmini, and including the fullpath to the command fixed the issue but here we are using the full path.

Check to make sure the Postgres daemon is running.

ASKER

berkeley:bin admin$ ps aux | grep postgres
_postgres 13539 0.4 0.0 2439388 628 ?? Ss Wed03AM 0:21.35 postgres: stats collector process
_postgres 13538 0.1 0.0 2479828 1916 ?? Ss Wed03AM 0:12.35 postgres: autovacuum launcher process
_postgres 13535 0.0 0.0 2479696 3872 ?? Ss Wed03AM 0:01.98 postgres: checkpointer process
_postgres 13533 0.0 0.0 2439388 392 ?? Ss Wed03AM 0:02.65 postgres: logger process
_postgres 13523 0.0 0.0 2479696 7724 ?? Ss Wed03AM 0:11.71 /Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_real -D /Library/Server/PostgreSQL For Server Services/Data -c listen_addresses= -c log_connections=on -c log_directory=/Library/Logs/PostgreSQL -c log_filename=PostgreSQL_Server_Services.log -c log_line_prefix=%t -c log_lock_waits=on -c log_statement=ddl -c logging_collector=on -c unix_socket_directory=/Library/Server/PostgreSQL For Server Services/Socket -c unix_socket_group=_postgres -c unix_socket_permissions=0770
_postgres 1864 0.0 0.0 2439388 592 ?? Ss Tue01PM 0:21.12 postgres: stats collector process
_postgres 1863 0.0 0.0 2479828 1816 ?? Ss Tue01PM 0:13.90 postgres: autovacuum launcher process
_postgres 1862 0.0 0.0 2479696 584 ?? Ss Tue01PM 0:04.26 postgres: wal writer process
_postgres 1861 0.0 0.0 2479696 788 ?? Ss Tue01PM 0:04.21 postgres: writer process
_postgres 1859 0.0 0.0 2479696 1200 ?? Ss Tue01PM 0:00.17 postgres: checkpointer process
_postgres 1857 0.0 0.0 2439388 400 ?? Ss Tue01PM 0:00.00 postgres: logger process
_postgres 1846 0.0 0.0 2479696 7732 ?? Ss Tue01PM 0:05.05 /Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_real -D /Library/Server/PostgreSQL/Data -c listen_addresses=127.0.0.1,::1 -c log_connections=on -c log_directory=/Library/Logs/PostgreSQL -c log_filename=PostgreSQL.log -c log_line_prefix=%t -c log_lock_waits=on -c log_statement=ddl -c logging_collector=on -c unix_socket_directory=/private/var/pgsql_socket -c unix_socket_group=_postgres -c unix_socket_permissions=0770
admin 47643 0.0 0.0 2432768 620 s001 S+ 11:07AM 0:00.00 grep postgres
_postgres 26777 0.0 0.0 2480724 6404 ?? Ss Wed10AM 0:00.14 postgres: webauth webauth [local] idle
_postgres 13537 0.0 0.0 2479696 636 ?? Ss Wed03AM 0:03.36 postgres: wal writer process
_postgres 13536 0.0 0.0 2479696 788 ?? Ss Wed03AM 0:03.32 postgres: writer process

ASKER

It's still serving my wiki pages.

Try logging in as the Postgres user and connecting to the template1 database, or running the commands.

ASKER

is that sudo su - postgres? never connected to template1 database.

ASKER

berkeley:bin admin$ sudo su -postgres
Password:
su: illegal option -- p
usage: su [-] [-flm] [login [args]]
berkeley:bin admin$ sudo su -postgres
su: illegal option -- p
usage: su [-] [-flm] [login [args]]
berkeley:bin admin$ sudo su - postgres
su: unknown login: postgres
berkeley:bin admin$

Interesting... after taking a look at the output from you running the "ps aux" command, it seems the username is "_postgres" (unless I'm misinterpretting something). So try sudo'ing as "_postgres"

ASKER

berkeley:bin admin$ /usr/bin/pg_dumpall -U _postgres >/tmp/cluster`date +\%Y\%m\%d`.dump
pg_dumpall: could not connect to database "template1": could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

berkeley:bin admin$ sudo /usr/bin/pg_dumpall -U _postgres >/tmp/cluster`date +\%Y\%m\%d`.dump
server version: 9.2.1; pg_dumpall version: 9.1.4
aborting because of server version mismatch
berkeley:bin admin$

ASKER

Trying to sudo su - _postgres just returned me to the command prompt instead of bringing me over to the postgres command prompt. I will try remote session to the postgres user

ASKER

that is a failure. _postgres does not appear....

It's possible that the system is setup so that _postgres isn't allowed to have a shell. It seems like all this is just moving us backward instead of forward, so let's consider a different approach.

Instead, try reinstalling the latest version of PostgreSQL manually. That should get all the binaries to be all the same version. Once that's done, the permissions error might just go away.

ASKER

Ouch, yeah that may be the issue, and the solution. Going to be a tough sell to the boss. Because that involves taking down my pages, and running that gambit. I have had to do this before. I had a similar db issue on my other server post update. I ended up gutting the entire mac osx server app, since it was not required.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

The comment by crazed sanity inspired me to take this to the Apple forums to see if anyone there knew what I was talking about, or had already gone through this issue on their own. After repeating some of my trouble shooting steps with another expert, a senior forum member came through, and offered the solution that I posted above. This solution turned out to be the correct one. This solved my problems, and I was able to make my backups, and move on.