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.
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”.
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.
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.
There are three fundamentally different approaches to backing up PostgreSQL data: SQL dump. File system level backup. Continuous archiving.
The usual process is:
pg_dumpall --globals-only
to get users/roles/etcpg_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 GRANT
s on DATABASE
s. 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 andALTER DATABASE SET
variable settings, were only dumped bypg_dumpall
. Nowpg_dump --create
andpg_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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With