Pg_dumpall apparently has a -globals option that's supposed to backup everything, but the help for pg_dumpall shows a -g, --globals-only dump only

994 Asked by AlexanderCoxon in SQL Server , Asked on Jul 2, 2024

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 developer restore a backup I took from PgadminIII via custom, directory, and tar format while selecting OIDs but he said two of them didn't load, tar did but it was only the directory, not the data. I'm really confused now.

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 one day at a time.

pg_dumpall apparently has a -globals option 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 the documentation, 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 option.

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 anyone has any suggestions on how to take a real backup in PostgreSQL and restore, I'd be very grateful.

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.

Answered by alex GONZALEZ

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>

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

<br>

Your Answer

Answer (1)

Ranjana

You are correct that there is some confusion around the options for pg_dumpall. The pg_dumpall utility is used to dump the entire contents of a PostgreSQL database cluster, including all databases and global objects such as roles and tablespaces.

The -g or --globals-only option specifically dumps only the global objects, without the actual database contents. Here's a clarification of the options:

-g, --globals-only: This option tells pg_dumpall to dump only the global objects. This includes roles and tablespaces, but not the data within the individual databases.

There is no -globals option in pg_dumpall. The confusion might come from misunderstanding the documentation or the way options are presented. The correct usage to dump all global objects (roles, tablespaces) is indeed pg_dumpall -g or pg_dumpall --globals-only.

Here's how you can use these options:

To dump everything (all databases and global objects):

  pg_dumpall > all_databases.sqlTo dump only global objects:pg_dumpall -g > globals.sql

If you want to back up everything (all databases and global objects), you simply run pg_dumpall without any special options. The -g option is specifically for when you only need the global objects.

For further details, you can refer to the official PostgreSQL documentation for pg_dumpall here.

5 Months