Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
4

I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users.

From what I've read, and it could be wrong, finding a good PostgreSQL blog has been challenging so please feel free to recommend some to me, I need to figure out how this app works so I can have trust in my backups and Slony replication. I had a dev restore a backup I took from PgadminIII via 'custom', 'directory', and 'tar' format while selecting 'OIDs' but he said 2 of them didn't load, tar did but it was only the directory, not the data. I'm really confused now.

  1. I am using PGAdminIII, it has a pg_dump and pg_dumpall option. I want to back everything up that I need to test restore this database somewhere and verify that yes, all the data we need and our backup is good. Eventually I want to write an auto-restore script but 1 day at a time. pg_dumpall apparently has a -globals command that's supposed to backup everything, but the help for pg_dumpall shows a "-g, --globals-only dump only global objects, no databases", not a --globals option.

    I thought pg_dumpall would at least backup foreign keys, but even that seems to be an 'option'. According to: http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even with pg_dumpall I need to use a -o option to backup foreign keys, I can't really imagine when I wouldn't want to backup foreign keys and this would make more sense as a default options.

  2. How would I take care of orphaned users and validate I have everything? I'd like to actually restore my backup file on another server and verify everything works. If anyoen has any suggestions on how to take a real backup in PostgreSQL and restore, I'd be very grateful. Thank you.

I hope I don't sound like some elitist but thus far I'm very disapointed with PostgreSQL backups, most likely it has to do with being a total PostgreSQL noob than anything else. I had a PostgreSQL server but I still can't fathom why the app would not backup OIDs by default! It seems like 99.9% of the time you would want that. Thanks.

UPDATE 1: Quick update: The postgres sites write up on http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html mentions that the globals option I was looking for seems to be a default option on this version, but it still needs the -o command. If someone can verify or give me an example command to restore a single database elsewhere with everything it needs I'd appreciate it. Thanks!

share|improve this question
 
Quick update: The postgres sites write up on postgresql.org/docs/9.1/static/app-pg-dumpall.html –  SQL-Learner May 11 '12 at 2:06
3  
What do you mean by orphaned users? In general, it sounds like you might be over-thinking things. If you run pg_dumpall > outfile you should be able to restore with psql -f infile postgres. In a default environment, you don't need any other options or fancy steps. That said, there are other backup techniques which fit different needs. Be sure to read this chapter when you get a chance: postgresql.org/docs/9.1/interactive/backup.html –  kgrittn May 11 '12 at 2:37
3  
If you're using OIDs in your tables you should replace them with primary keys of type serial or bigserial. There are too many reasons to include in a comment. Suffice it to say that it is old technology which may never be eliminated from system tables because of backward compatibility issues, but should never be used in user tables these days. postgresql.org/docs/9.1/interactive/… You don't need to include OIDs in the dump for the system tables on the restored database to be correct. –  kgrittn May 11 '12 at 3:10
1  
The --oids parameter refers to backing up "large objects" if I'm not mistaken. It does not relate to the oid column that is present in some system tables. You should not use OIDs as primary keys in your regular tables (which is also documented in the CREATE TABLE statement) –  a_horse_with_no_name May 11 '12 at 9:59
1  
Another thing: due to the different concepts, there is no such thing as an "orphaned" user in PostgreSQL as it does not have the distinction between "logins" and "users" (which is something I have never understood in SQL Server...) –  a_horse_with_no_name May 11 '12 at 10:01
show 1 more comments

migrated from stackoverflow.com May 11 '12 at 13:56

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 8 down vote accepted

You can dump the whole PostgreSQL cluster with pg_dumpall. That's all the databases and all the globals for a single cluster. From the command line on the server, I'd do something like this. (Mine's listening on port 5433, not on the default port.) You may or may not need the --clean option.

$ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql

This includes the globals--information about users and groups, tablespaces, and so on.

If I were going to backup a single database and move it to a scratch server, I'd dump the database with pg_dump, and dump the globals with either

  • pg_dumpall --globals-only, or
  • pg_dumpall --roles-only (if you only need roles)

like this.

$ pg_dump -U postgres -h localhost -p 5433 --clean --file=sandbox.sql sandbox
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --globals-only --file=globals.sql

Outputs are just text files.

After you move these files to a different server, load the globals first, then the database dump.

$ psql -U postgres -h localhost -p 5433 < globals.sql
$ psql -U postgres -h localhost -p 5433 < sandbox.sql

I thought pg_dumpall would at least backup foreign keys, but even that seems to be an 'option'. According to: http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even with pg_dumpall I need to use a -o option to backup foreign keys

No, that reference says "Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used." (Emphasis added.) I think it's unlikely that your application references the OID columns. You don't need to use this option to "backup foreign keys". (Read the dump file in your editor or file viewer.)

share|improve this answer
 
Thank you Catcall I'll give that a go, I'm on Windows sadly and don't have access to the Linux box but I'll see what I can do. –  SQL-Learner May 11 '12 at 3:29
1  
PostgreSQL works fine on Windows, too. –  Mike Sherrill 'Catcall' May 11 '12 at 3:51
 
is it required that the existing sandbox database be dropped, or the data dirs removed, prior to executing the 'psql -U postgres -h localhost -p 5433 < sandbox.sql' above? –  user11037 Aug 22 '12 at 16:32
3  
@Dooher: never mess with the data directory manually. It is required in order to start the PostgreSQL server. If you run pg_dumpall with the --clean option it will include the approriate DROP DATABASE commands for each database that is included in the dump. If your target cluster contains other databases, they won't be dropped (or overwritten). –  a_horse_with_no_name Aug 22 '12 at 17:56
 
Thanks for the extra clarification. –  Ali Razeghi Oct 30 '12 at 18:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.