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.

Answer 1

Backup:

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

Restore:

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

Answer 2

Restoring a postgres backup file depends on how you made 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

Answer 3

See below example it works:

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

Answer 4

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

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

2. 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.

Answer 5

Backup==>

Option 1: To take backup along with password in cmd

1.PGPASSWORD="mypassword" pg_dump -U postgres -h localhost --inserts mydb>mydb.sql

Option 2: To take backup without password in cmd

2. pg_dump -U postgres -h localhost --inserts mydb>mydb.sql

Option 3: 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

Answer 6

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.

Answer 7

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 your psql service in windows services (Win + R --> services.msc).

Answer 8

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"

Answer 9

Maybe try this command it will work

psql -U postgres -W -d Test2 < E:\SampleProject\samplebackup.sql

Answer 10

You can simply use the below command on the terminal

psql -U database_username -d database_name < dump.sql

Answer 11

Error: pg_restore: error: unsupported version (1.15) in file header

hint - it can be 1.13, 1.14, etc.

Quick Answer

1. sudo apt update
2. sudo apt install postgresql
3. Backup using plain format e.g. (backup.sql)
4. psql --host "localhost" --port "5432" --username "your-username" --dbname "your-database-name" --file "path-to-your-folder-where-the-backup-is/backup.sql"

Answer 12

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 that you usually want.

Dump the DB to a file mydb.psql:

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

Restore the DB from a file mydb.psql:

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

Some of the flags:

• -Fc: Format "custom", which happens to also be compressed as opposed to plaintext, and produces a much smaller dump file. Docs: https://www.postgresql.org/docs/current/app-pgdump.html file tmp.psql says:

  tmp.psql: PostgreSQL custom database dump - v1.14-0
  

  Compression can also be further customized with the -Z option.

• --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. If you don't pass this, it attempts to just add the dump on top of pre-existing data, which could fail unique constraints and corrupt your data.

• -v: verbose, output logs while restore. Can be helpful do debug things

• --no-privileges --no-owner: prevent issuing GRANT commands that setup privileges. These can cause trouble e.g. if you are restoring a production DB locally to reproduce, as the production and local may have different privilege setups. See also: pg_restore error: role XXX does not exist

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.

Answer 13

For taking a backup, one can use pg_dump command and for restoring psql command can be useful.

I did it by connecting an FTP of ec2 instance via putty, and ran the above commands as below. [pgclient should be installed on an ec2].

For backup,

pg_dump -h endpointOfSourceDBName -Fp --no-acl --no-owner -U postgres -t "\"Emp\""  SourceDBName > OutputFileName.sql  

where,
-F format
--format=format
p
plain
c
custom
d
directory
t
tar

-O
--no-owner

-t pattern
--table=pattern

-x
--no-privileges
--no-acl

For restoring,

psql \
   -f sourcefilename.sql \
   --host endPointOfDestinationDB \
   --port 5432 \
   --username postgres \
   --password passwordOfDestinationDB \
   --dbname destinationDBName

Answer 14

For example, you export and archive the schema and data or only the schema of apple database of the user(role) john to backup.sql with -Fc or -Ft as shown below. *backup.sql is created if it doesn't exist and my answer explains how to export and archive a database with -Fc or -Ft and my answer explains how to import backup.sql into orange database and the doc explains how to export and import databases:

pg_dump -U john -Fc apple > backup.sql

Or:

pg_dump -U john -Ft apple > backup.sql

Or:

pg_dump -U john -s -Fc apple > backup.sql

Or:

pg_dump -U john -s -Ft apple > backup.sql

Or, you export and archive(-Fc or -Ft) only the data of the specific tables of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below:

pg_dump -U john -Fc -a --column-inserts -t person -t animal apple > backup.sql

Or:

pg_dump -U john -Ft -a --column-inserts -t person -t animal apple > backup.sql

Then, you need to input a password after running the command above:

Password:

Now, you can import archive backup.sql into orange database of the user(role) john with pg_restore which must be used to import archive files as shown below. *You have to create orange database before hand otherwise there is error and my answer explains how to create a database:

pg_restore -U john -d orange < backup.sql

Or:

pg_restore --username=john --dbname=orange < backup.sql

Or:

pg_restore --username john --dbname orange < backup.sql

Then, you need to input a password after running the command above:

Password for user john:

In addition, you can import backup.sql into orange database of the user(role) john without a password prompt by setting a password(e.g., banana) to PGPASSWORD as shown below:

PGPASSWORD=banana pg_restore -U john -d orange < backup.sql

Answer 15

For example, you export the schema and data or only the schema of apple database of the user(role) john to backup.sql with psql which must be used to import non-archive files as shown below. *backup.sql is created if it doesn't exist and my answer explains how to export schema and data and my answer explains how to import archive backup.sql into orange database and the doc explains how to export and import databases:

pg_dump -U john apple > backup.sql

Or:

pg_dump -U john -s apple > backup.sql

Or, you export only the data of the specific tables of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below:

pg_dump -U john -a --column-inserts -t person -t animal apple > backup.sql

Then, you need to input a password after running the command above:

Password:

Now, you can import backup.sql into orange database of the user(role) john as shown below. *You have to create orange database before hand otherwise there is error and my answer explains how to create a database:

psql -U john -f backup.sql orange

Or:

psql -U john -d orange -f backup.sql

Or:

psql --username=john --file=backup.sql orange

Or:

psql --username john --file backup.sql orange

Or:

psql --username=john --dbname=orange --file=backup.sql

Or:

psql --username john --dbname orange --file backup.sql

Or, you can try these below which don't work on Windows:

psql -U john orange < backup.sql

psql -U john -d orange < backup.sql

psql --username=john --dbname=orange < backup.sql

psql --username john --dbname orange < backup.sql

Then, you need to input a password after running the command above:

Password for user john:

In addition, you can import backup.sql into orange database of the user(role) john without a password prompt by setting a password(e.g., banana) to PGPASSWORD as shown below:

PGPASSWORD=banana psql -U john -f backup.sql orange

Answer 16

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

1. Start the container

   docker start <postgres_container_id>
   

2. Access bash inside container

   docker exec -it <postgres_container_id> bash
   

3. Copy the .tar backup file to docker container (In another window)

   docker cp postgres_dump.tar <postgres_container_id>:/
   

4. Restore the backup

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

5. Enter password

Answer 17

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
  

Answer 18

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.

Answer 19

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

Answer 20

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

Answer 21

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)

Answer 22

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"

Answer 23

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

Answer 24

Follow these 3 steps :

1. start postgres server - sudo systemctl start postgresql
2. enable same - sudo systemctl enable postgresql
3. 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

Answer 25

This worked for me:

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

Answer 26

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

1. I had to change directory to my pg_hba.conf.
2. I had to edit the file to change method from peer to md5 as stated here
3. Restart the service: service postgresql-10 restart

4. 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.

Answer 27

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.

Answer 28

try this:

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

Restore DB psql from .sql file

Answer 29

try:

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

Answer 30

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>