Course Content
Introduction and Architectural overview
0/9
How does a computer work
History of PostgreSQL
Major Features – 1
Major Features – 2
Major Features – 3
Major Features – 4
Major Features – 5
General Database Limits
Where are my database files?
PostgreSQL Architecture
0/8
How a connection is established in PostgreSQL
PostgreSQL architecture overview
PostgreSQL Process Architecture
PostgreSQL Checkpointer
Logger, Stats collector and Autovacuum launcher
Background writer and WAL writer
Shared Memory Architecture
Local Memory Architecture
ACID Properties
0/2
ACID Properties
Path of the Query
Database Layout
0/4
Datafiles
WAL Files
Controlfile
PostgreSQL Authentication and Configuration Files
Logical Database Layout
0/4
Tablespaces
Databases
Schemas
Users and Roles
Backup and Recovery
0/3
Physical Backup
pg_basebackup
Logical Backups
PostgreSQL Tutorial for Absolute Beginners [Administration]
About Lesson

pg_dump

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql. Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications, even on other SQL database products.

The alternative archive file formats must be used with pg_restore to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

Pg_dump – -help

Man pg_dump

pg_restore

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved. The archive files also allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive files are designed to be portable across architectures.

pg_restore can operate in two modes. If a database name is specified, pg_restore connects to that database and restores archive contents directly into the database. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output. This script output is equivalent to the plain text output format of pg_dump. Some of the options controlling the output are therefore analogous to pg_dump options.

Obviously, pg_restore cannot restore information that is not present in the archive file. For instance, if the archive was made using the “dump data as INSERT commands” option, pg_restore will not be able to load the data using COPY statements.

Example., pg_restore -d newdb db.dump

Join the conversation
Previous
Next