• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1340
  • Last Modified:

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$
0
Illyankesh
Asked:
Illyankesh
  • 16
  • 8
2 Solutions
 
crazedsanityCommented:
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

0
 
IllyankeshAuthor Commented:
*******:~ 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
0
 
crazedsanityCommented:
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

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
IllyankeshAuthor Commented:
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
0
 
IllyankeshAuthor Commented:
berkeley:~ admin$ find /usr -name pg_dump
/usr/bin/pg_dump

The find returns only the one instance
0
 
IllyankeshAuthor Commented:
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$
0
 
crazedsanityCommented:
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

0
 
IllyankeshAuthor Commented:
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$
0
 
IllyankeshAuthor Commented:
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.
0
 
crazedsanityCommented:
Check to make sure the Postgres daemon is running.
0
 
IllyankeshAuthor Commented:
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
0
 
IllyankeshAuthor Commented:
It's still serving my wiki pages.
0
 
crazedsanityCommented:
Try logging in as the Postgres user and connecting to the template1 database, or running the commands.
0
 
IllyankeshAuthor Commented:
is that sudo su - postgres?  never connected to template1 database.
0
 
IllyankeshAuthor Commented:
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$
0
 
crazedsanityCommented:
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"
0
 
IllyankeshAuthor Commented:
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$
0
 
IllyankeshAuthor Commented:
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
0
 
IllyankeshAuthor Commented:
that is a failure.  _postgres does not appear....
0
 
crazedsanityCommented:
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.
0
 
IllyankeshAuthor Commented:
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.
0
 
crazedsanityCommented:
Granted I know virtually nothing about Mac OSX, but on all other operating systems, reinstalling PostgreSQL isn't that big of a deal.  Especially when the database is already running at the newest version and the problem is with the supporting binaries.

One possibility, depending upon what resources are available, is just to make a virtual clone of the machine, or just cloning the hard drive (using dd or ddrescue is pretty handy for that, though not necessarily the fastest/most efficient).  If you can make a virtual instance, by running a P2V (physical to virtual) conversion, you can test on the VM before doing anything to the physical machine.  And the bonus is you'll have a failsafe in the event of an irreversible problem.
0
 
IllyankeshAuthor Commented:
So I ran this by the apple support forums to see what the mac guys had to say.  Much like we did earlier we ran through the same steps.  We were about to go with a different set of arguments in the pg_dumpall command, when another expert chimed in.  The updated postgres has a different pathing.   The correct path is below.    So the actual command to complete the dumpall is

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

The final command looks like this.

PGUSER=_postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/pg_dumpall >/yourdrive`date +\%Y\%m\%d`.dump
0
 
IllyankeshAuthor Commented:
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.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 16
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now