Solved

Postgres manual db_dumpall fails

Posted on 2013-02-07
24
1,139 Views
Last Modified: 2013-02-17
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
Comment
Question by:Illyankesh
  • 16
  • 8
24 Comments
 
LVL 9

Expert Comment

by:crazedsanity
ID: 38868087
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
 

Author Comment

by:Illyankesh
ID: 38868815
*******:~ 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
 
LVL 9

Expert Comment

by:crazedsanity
ID: 38868852
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
 

Author Comment

by:Illyankesh
ID: 38868860
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
 

Author Comment

by:Illyankesh
ID: 38868864
berkeley:~ admin$ find /usr -name pg_dump
/usr/bin/pg_dump

The find returns only the one instance
0
 

Author Comment

by:Illyankesh
ID: 38868963
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
 
LVL 9

Expert Comment

by:crazedsanity
ID: 38869028
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
 

Author Comment

by:Illyankesh
ID: 38869043
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
 

Author Comment

by:Illyankesh
ID: 38869045
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
 
LVL 9

Expert Comment

by:crazedsanity
ID: 38869061
Check to make sure the Postgres daemon is running.
0
 

Author Comment

by:Illyankesh
ID: 38869080
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
 

Author Comment

by:Illyankesh
ID: 38869081
It's still serving my wiki pages.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 9

Expert Comment

by:crazedsanity
ID: 38869174
Try logging in as the Postgres user and connecting to the template1 database, or running the commands.
0
 

Author Comment

by:Illyankesh
ID: 38869431
is that sudo su - postgres?  never connected to template1 database.
0
 

Author Comment

by:Illyankesh
ID: 38869450
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
 
LVL 9

Expert Comment

by:crazedsanity
ID: 38869493
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
 

Author Comment

by:Illyankesh
ID: 38869521
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
 

Author Comment

by:Illyankesh
ID: 38869817
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
 

Author Comment

by:Illyankesh
ID: 38869840
that is a failure.  _postgres does not appear....
0
 
LVL 9

Expert Comment

by:crazedsanity
ID: 38874174
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
 

Author Comment

by:Illyankesh
ID: 38874703
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
 
LVL 9

Assisted Solution

by:crazedsanity
crazedsanity earned 500 total points
ID: 38874716
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
 

Accepted Solution

by:
Illyankesh earned 0 total points
ID: 38881141
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
 

Author Closing Comment

by:Illyankesh
ID: 38898178
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Are you new to OS X?  This helpful advice could get you quickly up to speed if you are making the transition from windows or totally new to OS X. Finder gives you the visual connection between you and the files located on the hard drive of your A…
The error "There was an error performing the update" occurred on a Mac OS X client workstation running  Symantec AntiVirus for Mac (http://www.symantec.com/business/products/purchasing.jsp?pcid=pcat_security&pvid=825_1) - the Enterprise product vers…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now