Restore a postgres backup file using the command l

问题:

I'm new to postgresql, and 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, so, if someone can tell me how to go about this, that'd be terrific!

回答1:

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

回答2:

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

回答3:

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

回答4:

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.

回答5:

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

回答6:

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/9.1/static/backup-dump.html

回答7:

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

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

回答8:

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"

回答9:

try this:

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

Restore DB psql from .sql file

回答10:

This worked for me:

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

回答11:

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

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

回答12:

try:

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

回答13:

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

回答14:

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

回答15:

Try to see if the following commands can help you:

sudo su - yourdbuser
psql
\i yourbackupfile

回答16:

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>

回答17:

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.

回答18:

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.

回答19:

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.

回答20:

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.

回答21:

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"