How To Backup and Restore PostgreSQL Database Using pg_dump and psql

pg_dump is an effective tool to backup postgres database. It creates a *.sql file with CREATE TABLE, ALTER TABLE, and COPY SQL statements of source database. To restore these dumps psql command is enough.
Using pg_dump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use pg_dump to backup and restore.

For the impatient, here is the quick snippet of how backup and restore postgres database using pg_dump and psql:



Backup:  $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}
Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

How To Backup Postgres Database

1. Backup a single postgres database

This example will backup erp database that belongs to user geekstuff, to the file mydb.sql


$ pg_dump -U geekstuff erp -f mydb.sql


It prompts for password, after authentication mydb.sql got created with create table, alter table and copy commands for all the tables in the erp database. Following is a partial output of mydb.sql showing the dump information of employee_details table.


— Name: employee_details; Type: TABLE; Schema: public; Owner: geekstuff; Tablespace:
CREATE TABLE employee_details (
employee_name character varying(100),
emp_id integer NOT NULL,
designation character varying(50),
comments text
);
ALTER TABLE public.employee_details OWNER TO geekstuff;

— Data for Name: employee_details; Type: TABLE DATA; Schema: public; Owner: geekstuff
COPY employee_details (employee_name, emp_id, designation, comments) FROM stdin;
geekstuff 1001 trainer
ramesh 1002 author
sathiya 1003 reader
.
— Name: employee_details_pkey; Type: CONSTRAINT; Schema: public; Owner: geekstuff; Tablespace:
ALTER TABLE ONLY employee_details
ADD CONSTRAINT employee_details_pkey PRIMARY KEY (emp_id);


2. Backup all postgres databases

To backup all databases, list out all the available databases as shown below.

Login as postgres / psql user:


$ su postgres


List the databases:


$ psql -l
List of databases
Name | Owner | Encoding
———–+———–+———-
article | sathiya | UTF8
backup | postgres | UTF8
erp | geekstuff | UTF8
geeker | sathiya | UTF8


Backup all postgres databases using pg_dumpall:

You can backup all the databases using pg_dumpall command.


$ pg_dumpall > all.sql


Verify the backup:

Verify whether all the databases are backed up,


$ grep “^[]connect” all.sql
\connect article
\connect backup
\connect erp
\connect geeker


3. Backup a specific postgres table


$ pg_dump –table products -U geekstuff article -f onlytable.sql


To backup a specific table, use the –table TABLENAME option in the pg_dump command. If there are same table names in different schema then use the –schema SCHEMANAME option.

How To Restore Postgres Database

1. Restore a postgres database


1. Restore a postgres database


$ psql -U erp -d erp_devel -f mydb.sql


This restores the dumped database to the erp_devel database.

Restore error messages

While restoring, there may be following errors and warning, which can be ignored.



psql:mydb.sql:13: ERROR: must be owner of schema public
psql:mydb.sql:34: ERROR: must be member of role “geekstuff”
psql:mydb.sql:59: WARNING: no privileges could be revoked
psql:mydb.sql:60: WARNING: no privileges could be revoked
psql:mydb.sql:61: WARNING: no privileges were granted
psql:mydb.sql:62: WARNING: no privileges were granted


2. Backup a local postgres database and restore to remote server using single command:


$ pg_dump dbname | psql -h hostname dbname


The above dumps the local database, and extracts it at the given hostname.

3. Restore all the postgres databases


$ su postgres
$ psql -f alldb.sql


4. Restore a single postgres table

The following psql command installs the product table in the geek stuff database.


$ psql -f producttable.sql geekstuff


 
Posted By Nijakat Ali

Be the first to comment

Leave a Reply

Your email address will not be published.


*