≡ Menu

How To Backup and Restore PostgreSQL Database Using pg_dump and psql

PostgreSQL DB Backup and RestoreThis is a guest post written by SathiyaMoorthy

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

$ 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


This article was written by SathiyaMoorthy, developer of Enterprise Postgres Query Analyser, an efficient tool for parsing postgresql log to generate html report, which can be used for fine tuning the postgres settings, and sql queries. The Geek Stuff welcomes your tips and guest articles.

Add your comment

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

Comments on this entry are closed.

  • user January 22, 2009, 5:56 am

    simple and best

  • Crovar January 22, 2009, 11:22 am

    Nice article…

  • rocio August 3, 2009, 10:55 am

    please, tell how can i backup and restore only the user roles, i tryed to use pg_dumpall but I couldn`t restore the file

  • augustine October 2, 2009, 9:24 am

    shall try the commands given
    then i shall modrate

  • Selvaganeshan October 11, 2009, 11:52 pm

    Can i take backup for two tables in single command line

    pg_dump -t table1 -t table2 -U db db -f table.sql
    Above command is taking dump for only table2

  • shikin August 12, 2010, 11:19 pm

    hi…i just backup all postgres on linux and what to restore to postgres window…
    -how to restore to postgres window if i use pg_dumpall command on linux?
    -how can i get the data backup?
    -how to i restore the backup on postgres window?

    Please help me….

  • Saad September 21, 2010, 1:38 am

    This is very easy understandable article.

    i want to take backup of some tables from another pg server and then want to restore it another server , can any one help me ?

    Saad

  • Lacy October 10, 2010, 5:35 am

    Thanks for this great article. Easy to understand.

  • Angel Mendoza November 22, 2011, 8:24 pm

    I tried so many things but nothing. Then I found this and I could do what I wanted. Next time I will spend time on some reading hehe!

  • Atul makwana May 18, 2012, 3:35 am

    i wanted to migrate the database table, this post is very useful for me to migrating database table ..

    Thanks for the Post..!!

  • Alejandro July 8, 2012, 1:45 pm

    Thanks, simple and useful.

    Roles and the rest of the settings are in postgresql database itself. With a dumpall roles migrates too.
    There is a lack of good psql admins open source, but with some time to spent, for sure someone could do one.

  • Prakash October 26, 2012, 12:28 am

    Hi,
    When we execute the Backup command, where that backup file will be saved?
    Is there any location? so i can copy to new location of same backup of database..

  • velmurugan November 7, 2012, 1:03 am

    You site is very useful for me for postgres commands.

  • Kannan November 16, 2012, 1:27 am

    Please share the details that how to schedule the backups in postgresql

  • Alabandit January 23, 2013, 9:33 am

    had to run with host name added for restore to work on ubuntu:
    psql -h localhost -U erp -d erp_devel -f mydb.sql

    Thanks!

  • Giovanni February 13, 2013, 8:54 am

    Thanks.

  • Joe May 24, 2013, 9:13 am

    When doing a restore of all the databases, should I clear out any information that’s already in the database I am restoring into?

  • Pankaj Prasun May 28, 2013, 10:11 am

    psql -U [user-name] -W -h localhost -d [desintation_db] -f Sqlfilename.sql and it will ask for password.

  • nnm July 18, 2013, 6:58 am

    [q]
    The following psql command installs the product table in the geek stuff database.
    $ psql -f producttable.sql geekstuff
    [/q]

    In fact this command executes the entire content of the file. If the file contains only the table definition then is ok, otherwise you might be in trouble. For selectively dumping and restoring tables use pg_dump and pg_restore with custom format.

  • tickalodaon April 17, 2014, 10:18 am

    thanks mate you save my worthed day to become couple minutes job done 🙂

  • Việt Mới March 19, 2015, 12:15 pm

    I have error in command :
    $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}
    in PostgreSQL 9.4.1 on window 8.
    “pg_dump: too many command-line arguments (first is “dumpfilename.sql”)
    Try “pg_dump –help” for more information.”
    When I edit your command:
    $ pg_dump -U {user-name} -d {source_db} -f {dumpfilename.sql}
    It’s works.
    Thanks

  • dmitry June 14, 2015, 10:31 pm

    quick backup, skipped flag -d (postgres 9.3)
    Backup: $ pg_dump -U {user-name} -d {source_db} -f {dumpfilename.sql}

  • nickel June 3, 2016, 5:00 am

    Hi which prompt I should try for pg_dump command. I am new to postgres.
    Tried logging in using psql -d dbname -U username
    when I tried the pg_dump command it did not respond.