Restore a postgres backup file using the command line?

Question

Locally, I use pgadmin3. On the remote server, however, I have no such luxury.

I've already created the backup of the database and copied it over, but is there a way to restore a backup from the command line? I only see things related to GUI or to pg_dumps.

score 653 · Accepted Answer · edited Jun 20 '22 at 16:36

653

There are two tools to look at, depending on how you created the dump file.

Your first source of reference should be the man page pg_dump as that is what creates the dump itself. It says:

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(1). 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(1) 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.

So depends on the way it was dumped out. If using Linux/Unix, you can probably figure it out using the excellent file(1) command - if it mentions ASCII text and/or SQL, it should be restored with psql otherwise you should probably use pg_restore.

Restoring is pretty easy:

psql -U username -d dbname < filename.sql

-- For Postgres versions 9.0 or earlier
psql -U username -d dbname -1 -f filename.sql

or

pg_restore -U username -d dbname -1 filename.dump

Check out their respective manpages - there's quite a few options that affect how the restore works. You may have to clean out your "live" databases or recreate them from template0 (as pointed out in a comment) before restoring, depending on how the dumps were generated.

edited Jun 20 '22 at 16:36

Steve Chambers

37,270
24
156
208

answered Apr 28 '10 at 19:25

Steven Schlansker

37,580
14
81
100

4

IME you practically always want to restore into a database that has been newly-created from template0. Otherwise if you've done something like activating plpgsql in template1, the restore process will try to do it again, and the -1 switch you suggest means the whole transaction will fail. So something like "createdb -T template0 seo2" followed by "pg_restore -v -d seo2 seo.pg" to restore seo.pg (made from the seo database) into a new seo2 database. If your backup file is just a .sql file, you can trim conflicting bits of it out by hand. – araqnid Apr 28 '10 at 19:37
Sometimes the dump explicitly drops the database and recreates it. But good point :-) – Steven Schlansker Apr 28 '10 at 20:08
How I put password into this template0? – Haseena Nov 23 '12 at 05:52
72

You cannot have `-d` and `-f` at the same time. `pg_restore: options -d/--dbname and -f/--file cannot be used together ` – CppLearner Feb 09 '13 at 00:37
9

In version 9.2 the "-f" option specifies an output file, not the dump file (and it probably means the same in earlier versions). – David Resnick Apr 06 '14 at 12:58
@araqnid - ta buddy. I tried what s in the answer but it complained you can't use -d and -f in one command. Your version worked. nice and easy to remember also. – Will Sep 03 '14 at 04:54
this didn't work for me, best answer should have been the one from pilot – Alberici May 07 '15 at 18:21
4

Regarding `cannot be used together`, see here: https://stackoverflow.com/questions/27882070/why-does-pg-restore-return-options-d-dbname-and-f-file-cannot-be-used-tog – Abdull Mar 23 '16 at 20:15
psql -h localhost -p 5432 -U myUsername mydbname< myDumpFile.dump – developer747 Dec 15 '16 at 20:43
3

@Alex78191 it means it executes as a single transaction, which could completely fail and rollback, or go through. It will block some access to the database also. – ATN Aug 24 '18 at 09:36
Can we add verbose to this restore command if yes please let me know-how? – Ashish Karpe Dec 03 '19 at 03:57
1

`psql -U USERNAME -d DBNAME -1 -f FILENAME.sql` failed with syntax errors where as `psql -U USERNAME -d DBNAME < FILENAME.sql` worked. – tharndt Jan 16 '20 at 09:48
I am not sure why this was voted up at all. A working pg_dump command that will work with the `psql` or `pg_restore` command is not even included. This is not an adequate, fully working example of anything helpful. Delete this answer, people clicked to upvote, not even trying this. sloppy. – user10664542 Feb 06 '20 at 07:22
To me, using [`\i or \include filename`](https://www.postgresql.org/docs/current/app-psql.html) worked better than `-f`. – Rafs Jul 06 '21 at 10:19
You probably need to create the new database which you're dumping into before you can restore: `createdb -U username dbname` – emilaz Aug 24 '21 at 10:04
can we add how to dump in this answer? – buncis Jun 08 '23 at 13:05

score 318 · Answer 2 · edited May 31 '22 at 19:22

318

create backup

pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f 
"/usr/local/backup/10.70.0.61.backup" old_db

-F c is custom format (compressed, and able to do in parallel with -j N) -b is including blobs, -v is verbose, -f is the backup file name.

restore from backup

pg_restore -h localhost -p 5432 -U postgres -d old_db -v 
"/usr/local/backup/10.70.0.61.backup"

important to set -h localhost - option

edited May 31 '22 at 19:22

Eduardo Cuomo

17,828
6
117
94

answered May 28 '12 at 08:55

Alex Deemann

3,586
2
14
9

4

I'd like to mention that before you execute these commands you should be located in the Posgresql **bin** folder. For example in Windows (if you installed it in the default folder) it would be **C:\Program Files\PostgreSQL\9.6\bin** – juan_carlos_yl Dec 27 '19 at 17:48
1

@juan_carlos_yl Or make sure the bin directory is on your PATH – fluffy_mart Sep 21 '21 at 08:08
2

>-F c is custom format (compressed, and able to do in parallel with -j N) This is wrong, only directory format support parallel dump – TrungNT Dec 26 '21 at 10:20
`pg_dump -h localhost -p 5432 -U postgres -F c -b -v old_db -f "/usr/local/backup/10.70.0.61.backup"` I find it more intuitive if tis written this way – buncis Jun 08 '23 at 13:09

Tombart · Answer 3 · 2018-09-20T07:42:43.017

138

You might need to be logged in as postgres in order to have full privileges on databases.

su - postgres
psql -l                      # will list all databases on Postgres cluster

pg_dump/pg_restore

  pg_dump -U username -f backup.dump database_name -Fc

switch -F specify format of backup file:

c will use custom PostgreSQL format which is compressed and results in smallest backup file size
d for directory where each file is one table
t for TAR archive (bigger than custom format)
-h/--host Specifies the host name of the machine on which the server is running
-W/--password Force pg_dump to prompt for a password before connecting to a database

restore backup:

   pg_restore -d database_name -U username -C backup.dump

Parameter -C should create database before importing data. If it doesn't work you can always create database eg. with command (as user postgres or other account that has rights to create databases) createdb db_name -O owner

pg_dump/psql

In case that you didn't specify the argument -F default plain text SQL format was used (or with -F p). Then you can't use pg_restore. You can import data with psql.

backup:

pg_dump -U username -f backup.sql database_name

restore:

psql -d database_name -f backup.sql

edited Sep 20 '18 at 07:42

answered Aug 31 '13 at 08:42

Tombart

30,520
16
123
136

2

I used the "psql -d database_name -f backup.sql" command to restore a database I dumped from dokku on DigitalOcean. Worked great. – NineBlindEyes Nov 24 '14 at 23:03
3

while using psql if your user (for e.g. `postgres`) has a password set then `-W` option should be used. For e.g. on Ubuntu without doing `su postgres` anywhere from terminal `$ psql -h localhost -U postgres -W -d DB_NAME < DB_BACKUP.sql` is the command which worked for me to restore my backup on my localhost. Note that `-h` option is needed. – Jignesh Gohel Sep 19 '18 at 11:25
1

@JiggneshhGohel As the manual says: `-W` option is never essential. `pg_dump` will automatically prompt for a password if the server demands password authentication. Also you can use `PGPASSWORD` env variable, if you're using plain-text passwords. `-h` is needed if the default `PGHOST` is not applicable. These options are common for many PostgreSQL utils, thus not essential to answer this question (it heavily depends on your setup). – Tombart Sep 20 '18 at 07:47
1

@Tombart your 2nd option `pg_dump/psql` part is work for me. but 1st part `pg_dump/pg_restore` is not work for me for restore. Thank you. – Anjan Biswas Jan 30 '19 at 04:51
1

@AnjanBiswas The first option is using compressed archive, which will need more CPUs but the backup file will occupy less space on disk. You just need to choose appropriate compression e.g. `-Fc` – Tombart Jan 31 '19 at 09:40

score 79 · Answer 4 · edited May 01 '17 at 06:07

79

POSTGRESQL 9.1.12

DUMP:

pg_dump -U user db_name > archive_name.sql

put the user password and press enter.

RESTORE:

psql -U user db_name < /directory/archive.sql

put the user password and press enter.

edited May 01 '17 at 06:07

Aamir

16,329
10
59
65

answered Sep 12 '14 at 22:14

Natan Medeiros

1,351
1
11
15

How i can capture restore log in file instead of printing on screen? please let me know i have tries with >>, 2>> , but not working thanks. – Adam Mulla Apr 07 '22 at 07:11

score 42 · Answer 5 · edited Mar 09 '18 at 14:26

42

Below is my version of pg_dump which I use to restore the database:

pg_restore -h localhost -p 5432 -U postgres -d my_new_database my_old_database.backup

or use psql:

psql -h localhost -U postgres -p 5432 my_new_database < my_old_database.backup

where -h host, -p port, -u login username, -d name of database

edited Mar 09 '18 at 14:26

Eduardo Cuomo

17,828
6
117
94

answered Aug 24 '16 at 12:43

Yahor M

617
8
8

3

Govind Singh, why did you edit my answer? your changes bring nothing new.. – Yahor M Nov 24 '17 at 06:58
2

`my_new_database` should already exist when using `psql`, not? – Elmex80s Jul 06 '18 at 13:37

Sarath Ak · Answer 6 · 2021-11-12T10:35:51.567

41

Backup and restore with GZIP

For larger size database this is very good

backup

pg_dump -U user -d mydb | gzip > mydb.pgsql.gz

restore

gunzip -c mydb.pgsql.gz | psql dbname -U user

https://www.postgresql.org/docs/14/backup-dump.html

edited Nov 12 '21 at 10:35

answered Oct 14 '16 at 07:13

Sarath Ak

7,903
2
47
48

2

For ubuntu you could use: `gunzip -c mydb.pgsql.gz | sudo -u postgres psql` To get the permissions to operate on the database. Also consider the `--clean` flag when dumping, that will wipe all existing data, might come in handy. – Sebastian Jun 29 '17 at 11:35
1

Working great and neat solution for me. Thank you! – Nam G VU Dec 11 '19 at 04:29
for windows it is hard to configure all eg. gungip command ;) – Learner May 27 '22 at 04:48

score 32 · Answer 7 · edited Jan 20 '20 at 15:26

32

This worked for me:

pg_restore --verbose --clean --no-acl --no-owner --host=localhost --dbname=db_name --username=username latest.dump

edited Jan 20 '20 at 15:26

Andreas Baumgart

2,647
1
25
20

answered Aug 10 '19 at 06:40

Francesco

321
3
2

6

Thanks, the `--no-owner` really helped me. – bonafernando Jan 02 '20 at 17:43

Aaron Lelevier · Answer 8 · 2014-07-30T16:15:27.447

18

Backup:  $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

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

edited Jul 30 '14 at 16:15

answered Feb 25 '14 at 15:20

Aaron Lelevier

19,850
11
76
111

DevonDahon · Answer 9 · 2022-08-12T05:19:08.883

11

Backup & Restore

This is the combo I'm using to backup, drop, create and restore my database (on macOS and Linux):

sudo -u postgres pg_dump -Fc mydb > ./mydb.sql
sudo -u postgres dropdb mydb
sudo -u postgres createdb -O db_user mydb
sudo -u postgres pg_restore -d mydb < ./mydb.sql

Misc

-Fc will compress the database (Format custom)
List PostgreSQL users: sudo -u postgres psql -c "\du+"
You may want to add hostname and date to ./mydb.sql, then change it by:
```
./`hostname`_mydb_`date +"%Y%m%d_%H%M"`.sql
```

edited Aug 12 '22 at 05:19

answered Oct 07 '20 at 13:09

DevonDahon

7,460
6
69
114

good stuff @DevonDahon, 4 commands that are straight to the point & doesn't need the psql shell. I was able successfully user your answer but had a warning `pg_restore: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used` on Ubuntu 18.04 with postgres 10.13 so I don't know what that's about... – JC23 Jul 31 '21 at 02:34

score 10 · Answer 10 · edited Feb 21 '19 at 19:16

10

try this:

psql -U <username> -d <dbname> -f <filename>.sql

Restore DB psql from .sql file

edited Feb 21 '19 at 19:16

Ehsan

604
7
21

answered Nov 19 '18 at 09:52

Faysal Maqsood

99
1
3

psql -U -d -f .sql – Faysal Maqsood Nov 19 '18 at 09:53

score 9 · Answer 11 · edited Jul 19 '21 at 16:52

1. Open the Terminal.

2. Backup your database with following command

your postgres bin -> /opt/PostgreSQL/9.1/bin/

your source database server -> 192.168.1.111

your backup file location and name -> /home/dinesh/db/mydb.backup

your source db name -> mydatabase

/opt/PostgreSQL/9.1/bin/pg_dump --host '192.168.1.111' --port 5432 --username "postgres" --no-password  --format custom --blobs --file "/home/dinesh/db/mydb.backup" "mydatabase"

3. Restore mydb.backup file into destination.

your destination server -> localhost

your destination database name -> mydatabase

Create database for restore the backup.

/opt/PostgreSQL/9.1/bin/psql -h 'localhost' -p 5432 -U postgres -c "CREATE DATABASE mydatabase"

Restore the backup.

/opt/PostgreSQL/9.1/bin/pg_restore --host 'localhost' --port 5432 --username "postgres" --dbname "mydatabase" --no-password --clean "/home/dinesh/db/mydb.backup"

score 8 · Answer 12 · answered May 30 '17 at 10:46

If you create a backup using pg_dump you can easily restore it in the following way:

Open command line window
Go to Postgres bin folder. For example: cd "C:\ProgramFiles\PostgreSQL\9.5\bin"
Enter the command to restore your database. For example: psql.exe -U postgres -d YourDatabase -f D:\Backup\.sql
Type password for your postgres user
Check the restore process

Igr Pn · Answer 13 · 2021-05-24T00:30:57.620

8

I didnt see here mentions about dump file extension (*.dump).

This solution worked for me:

I got a dump file and needed to recover it.

First I tried to do this with pg_restore and got:

pg_restore: error: input file appears to be a text format dump. Please use psql.

I did it with psql and worked well:

psql -U myUser -d myDataBase < path_to_the_file/file.dump

edited May 24 '21 at 00:30

answered Jun 23 '20 at 14:47

Igr Pn

131
2
3

score 8 · Answer 14 · answered Oct 06 '21 at 02:44

To restore a dump file

psql -d [Dbname] -U [UserName] -p 5432 < [FileLocation]

To restore a .SQL file

pg_restore -U [Username] -d [Dbname] -1 [FileLocation]

If you get user authentication errors, go to the file pg_hba.conf which is in PSQL/data folder in your program files, and change the "METHOD" to "Trust". Restart you psql serive in windows services(Win + R --> services.msc).

score 7 · Answer 15 · answered Oct 22 '18 at 06:56

7

1) Open psql terminal.

2) Unzip/ untar the dump file.

3) Create an empty database.

4) use the following command to restore the .dump file

<database_name>-# \i <path_to_.dump_file>

answered Oct 22 '18 at 06:56

Vaibhav Desai

2,334
2
25
29

score 6 · Answer 16 · answered Nov 19 '18 at 18:44

6

try:

pg_restore -h localhost -p 5432 -U <username> -d <dbname> -1 <filename>

answered Nov 19 '18 at 18:44

Cristhian Gonzalez

61
1
4

score 6 · Answer 17 · edited Aug 13 '23 at 14:08

If you are using docker, this answer may be helpful.

Start the container
```
docker start <postgres_container_id>
```

Access bash inside container

docker exec -it <postgres_container_id> bash

Copy the .tar backup file to docker container (In another window)
```
docker cp postgres_dump.tar <postgres_container_id>:/
```

Restore the backup

pg_restore -c -U <postgres-user> -d <database-name>  -v "postgres_dump.tar" -W

Enter password

score 5 · Answer 18 · answered Oct 14 '14 at 17:22

5

Restoring a postgres backup file depends on how did you take the backup in the first place.

If you used pg_dump with -F c or -F d you need to use pg_restore otherwise you can just use

psql -h localhost -p 5432 -U postgres < backupfile

9 ways to backup and restore postgres databases

answered Oct 14 '14 at 17:22

Prashant Kumar

678
6
7

1

Do you realize that you missed the database name part and it will complain about destination database doesn't exist? – Pere Jan 12 '17 at 08:11

score 4 · Answer 19 · answered May 06 '17 at 08:46

As below link said, you can use psql command for restoring the dump file:

https://www.postgresql.org/docs/8.1/static/backup.html#BACKUP-DUMP-RESTORE

psql dbname < infile

if you need to set username just add the username after the command like:

psql dbname < infile username

score 4 · Answer 20 · answered Dec 08 '21 at 13:51

4

The shortest way with no password prompt

psql "postgresql://<db_user>:<db_pass>@<ip>:<port>/<db_name>" < "backup.sql"

If you are using Windows OS

psql.exe "postgresql://<db_user>:<db_pass>@<ip>:<port>/<db_name>" < "backup.sql"

answered Dec 08 '21 at 13:51

Masih Jahangiri

9,489
3
45
51

Tim · Answer 21 · 2020-01-14T03:27:58.003

Sorry for the necropost, but these solutions did not work for me. I'm on postgres 10. On Linux:

I had to change directory to my pg_hba.conf.
I had to edit the file to change method from peer to md5 as stated here
Restart the service: service postgresql-10 restart
Change directory to where my backup.sql was located and execute:
psql postgres -d database_name -1 -f backup.sql

-database_name is the name of my database

-backup.sql is the name of my .sql backup file.

score 2 · Answer 22 · edited Aug 09 '17 at 15:54

2

Try to see if the following commands can help you:

sudo su - yourdbuser
psql
\i yourbackupfile

edited Aug 09 '17 at 15:54

Fabien

4,862
2
19
33

answered Aug 09 '17 at 14:27

Dr. Mehmet Ali ATICI

609
1
9
13

score 2 · Answer 23 · answered Mar 20 '19 at 12:38

If you have a backup SQL file then you can easily Restore it. Just follow the instructions, given in the below

1. At first, create a database using pgAdmin or whatever you want (for example my_db is our created db name)
2. Now Open command line window
3. Go to Postgres bin folder. For example:  cd "C:\ProgramFiles\PostgreSQL\pg10\bin"
4. Enter the following command to restore your database: psql.exe -U postgres -d my_db -f D:\Backup\backup_file_name.sql

Type password for your postgres user if needed and let Postgres to do its work. Then you can check the restore process.

score 1 · Answer 24 · answered Jul 19 '17 at 03:04

I was having authentication problems running pg_dump, so I moved my dump file

mv database_dump /tmp

into the temp directory and then ran

su -u postgres
cd /tmp
pg_restore database_dump

If you have a large database dump, you may just want to create another directory where your current user and the postgres user can access and putting the database dump file into that.

Ramesh Ponnusamy · Answer 25 · 2020-11-02T01:24:26.880

Backup==>

Option1: To take backup along with password in cmd
1.PGPASSWORD="mypassword" pg_dump -U postgres -h localhost --inserts mydb>mydb.sql
Option2: To take backup without password in cmd
2. pg_dump -U postgres -h localhost --inserts mydb>mydb.sql
Option3: To take backup as gzip(if database is huge)
3. pg_dump -U postgres -h localhost mydb --inserts | gzip > mydb.gz

Restore:
1. psql -h localhost -d mydb -U postgres -p 5432 < mydb.sql

score 1 · Answer 26 · answered Jun 14 '21 at 06:20

This solution only works for Windows.

First, ensure you have already added the postgres bin folder to the "Path" environment variable (in my case this folder is C:\Program Files\PostgreSQL\12\bin).

Then, open the Windows command interpreter (cmd), go to the folder where you have the .sql file and execute this command:

pg_restore -U userName -d database-1 backupfile.sql

For example:

pg_restore -U sam -d SamDataBase -1 SamDataBaseBackup.sql

(It can ask you for the password of the user so ensure to type it correctly and then click enter)

Pura vida!

score 1 · Answer 27 · answered Aug 25 '21 at 14:13

If you have created a new database named mydb, To restore a .sql dump to that database with psql,

psql --file=dump.sql --username=postgres --host=localhost --port=5432 mydb

the password will be prompted by psql

The connection options are

  -h, --host=HOSTNAME      database server host or socket directory (default: "/var/run/postgresql")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "xyz")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

score 1 · Answer 28 · answered Nov 18 '22 at 18:57

Save and restore the exact same state with compressed dump

Other answers gave all the key bits separately, but hopefully this will provide be the "just works save and restore to exact state" command pair.

Dump to file mydb.psql:

PGPASSWORD=mypassword pg_dump -U my_username -h localhost mydb -Fc -f mydb.psql

Restore:

PGPASSWORD=mypassword pg_restore -U my_username -h localhost \
  --clean -d mydb -v mydb.psql

Some of the flags:

-Fc: Format Compressed, as opposed to plaintext.

file tmp.psql says:
```
tmp.psql: PostgreSQL custom database dump - v1.14-0
```
--clean: destroy the target DB before restoring it, thus returning to the exact same pristine state.

Any data created after the dump will be lost.

PGPASSWORD, -U and -h can of course be modified depending on your login method, e.g. without PGPASSWORD you're prompted for a password, and none of those are needed if you set up peer auth locally.

Tested on Ubuntu 22.04, PostgreSQL 14.5.

score 0 · Answer 29 · edited May 26 '20 at 09:06

If you want to backup your data or restore data from a backup, you can run the following commands:

To create backup of your data, go to your postgres \bin\ directory like C:\programfiles\postgres\10\bin\ and then type the following command:
```
pg_dump -FC -U ngb -d ngb -p 5432 >C:\BACK_UP\ngb.090718_after_readUpload.backup
```
To restore data from a backup, go to your postgres \bin\ directory like C:\programfiles\postgres\10\bin\ and then type below command:
```
C:\programFiles\postgres\10\bin> pg_restore -Fc -U ngb -d ngb -p 5432 <C:\ngb.130918.backup
```
Please make sure that the backup file exists.

score -1 · Answer 30 · answered Jun 26 '20 at 10:49

Follow these 3 steps :

start postgres server - sudo systemctl start postgresql

enable same - sudo systemctl enable postgresql

restore command - pg_restore -h localhost -p 5432 -U postgres -d old_db

assuming that the dump is there in the same directory

Links :

https://www.postgresqltutorial.com/postgresql-restore-database https://askubuntu.com/questions/50621/cannot-connect-to-postgresql-on-port-5432

score -5 · Answer 31 · edited May 23 '15 at 06:35

-5

See below example its working

C:/Program Files/PostgreSQL/9.4/bin\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "newDatabase" --no-password --verbose

"C:\Users\Yogesh\Downloads\new Download\DB.backup"

edited May 23 '15 at 06:35

Anptk

1,125
2
17
28

answered May 23 '15 at 05:32

user3881346

5

Restore a postgres backup file using the command line?

31 Answers31

Backup & Restore

Misc

The shortest way with no password prompt

If you are using Windows OS

This solution only works for Windows.

Linked

Related