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!
This question is related to
postgresql
command-line
backup
restore
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
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"
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.
Try to see if the following commands can help you:
sudo su - yourdbuser
psql
\i yourbackupfile
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
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
If you create a backup using pg_dump you can easily restore it in the following way:
cd "C:\ProgramFiles\PostgreSQL\9.5\bin"
For example: psql.exe -U postgres -d YourDatabase -f D:\Backup\.sql
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
try:
pg_restore -h localhost -p 5432 -U <username> -d <dbname> -1 <filename>
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.
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 sized
for directory where each file is one tablet
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
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
-Fc
will compress the database (format custom)sudo -u postgres psql -c "\du+"
./mydb.sql
, then change it by:
./`hostname`_mydb_`date +"%Y%m%d_%H%M"`.sql
try this:
psql -U <username> -d <dbname> -f <filename>.sql
Restore DB psql from .sql file
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.
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
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
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.
Backup: $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}
Restore: $ psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql}
Sorry for the necropost, but these solutions did not work for me. I'm on postgres 10. On Linux:
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.
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
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>
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"
This worked for me:
pg_restore --verbose --clean --no-acl --no-owner --host=localhost --dbname=db_name --username=username latest.dump
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
Source: Stackoverflow.com