How To Backup and Restore PostgreSQL Database Using pg_dump and psql

本文介绍了使用pg_dump和psql备份与恢复Postgres数据库的方法。包括备份单个数据库、所有数据库、特定表,以及恢复数据库、将本地数据库备份并恢复到远程服务器、恢复所有数据库和单个表等操作,还提及了恢复时可能出现的错误。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

https://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/

by RAMESH NATARAJAN on JANUARY 21, 2009

 

Tweet

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.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值