Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

pg_dump vs pg_dumpall? which one to use to database backups?

I tried pg_dump and then on a separate machine I tried to import the sql and populate the database, I see

CREATE TABLE ERROR:  role "prod" does not exist CREATE TABLE ERROR:  role "prod" does not exist CREATE TABLE ERROR:  role "prod" does not exist CREATE TABLE ERROR:  role "prod" does not exist ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE WARNING:  no privileges could be revoked for "public" REVOKE ERROR:  role "postgres" does not exist ERROR:  role "postgres" does not exist WARNING:  no privileges were granted for "public" GRANT 

which means my user and roles and grant information is not in pg_dump

On the other hand we have pg_dumpall, I read conversation, and this does not lead me anywhere?

Question
- Which one should I be using for database backups? pg_dump or pg_dumpall?
- the requirement is that I can take the backup and should be able to import to any machine and it should work just fine.

like image 880
daydreamer Avatar asked May 17 '13 22:05

daydreamer


People also ask

What is the difference between pg_dump and Pg_dumpall?

Dumping Using pg_dump and pg_dumpall. The pg_dump utility can be used to generate a logical dump of a single database. If you need to include global objects (like uses and tablespaces) or dump multiple databases, use pg_dumpall instead. The output generated by pg_dump is not a traditional “backup”.

What is the default backup method used using pg_dump?

pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall. Dumps can be output in script or archive file formats.

How do I backup all Postgres databases?

To back up, a PostgreSQL database, start by logging into your database server, then switch to the Postgres user account, and run pg_dump as follows (replace tecmintdb with the name of the database you want to backup). By default, the output format is a plain-text SQL script file.

Which is an approach to backup PostgreSQL data?

There are three fundamentally different approaches to backing up PostgreSQL data: SQL dump. File system level backup. Continuous archiving.


1 Answers

The usual process is:

  • pg_dumpall --globals-only to get users/roles/etc
  • pg_dump -Fc for each database to get a nice compressed dump suitable for use with pg_restore.

Yes, this kind of sucks. I'd really like to teach pg_dump to embed pg_dumpall output into -Fc dumps, but right now unfortunately it doesn't know how so you have to do it yourself.

Up until PostgreSQL 11 there was also a nasty caveat with this approach: Neither pg_dump, nor pg_dumpall in --globals-only mode would dump user access GRANTs on DATABASEs. So you pretty much had to extract them from the catalogs or filter a pg_dumpall. This is fixed in PostgreSQL 11; see the release notes.

Make pg_dump dump the properties of a database, not just its contents (Haribabu Kommi)

Previously, attributes of the database itself, such as database-level GRANT/REVOKE permissions and ALTER DATABASE SET variable settings, were only dumped by pg_dumpall. Now pg_dump --create and pg_restore --create will restore these database properties in addition to the objects within the database. pg_dumpall -g now only dumps role- and tablespace-related attributes. pg_dumpall's complete output (without -g) is unchanged.


You should also know about physical backups - pg_basebackup, PgBarman and WAL archiving, PITR, etc. These offer much "finer grained" recovery, down to the minute or individual transaction. The downside is that they take up more space, are only restoreable to the same PostgreSQL version on the same platform, and back up all tables in all databases with no ability to exclude anything.

like image 64
Craig Ringer Avatar answered Sep 18 '22 14:09

Craig Ringer