Restore a postgres backup file using the command line

Question

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

User · Answer

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.

User · Answer

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  lt  path to the file file dump

User · Answer

Backup and restore with GZIP     For larger size database this is very good   backup  pg dump -U user -d mydb   gzip  gt  mydb pgsql gz   restore  gunzip -c mydb pgsql gz   psql dbname -U user   https   www postgresql org docs 9 1 static backup-dump html

User · Answer

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  lt  backupfile   9 ways to backup and restore postgres databases

User · Answer

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   lt database name gt -   i  lt path to  dump file gt

User · Answer

Backup  amp  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  gt    mydb sql sudo -u postgres dropdb mydb sudo -u postgres createdb -O db user mydb sudo -u postgres pg restore -d mydb  lt    mydb sql  Misc  -Fc will compress the database  format custom  List PostgreSQL users  sudo -u postgres psql -c  quot  du  quot  You may want to add hostname and date to   mydb sql  then change it by     hostname  mydb  date   quot  Y m d  H M quot   sql

User · Answer

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

User · Answer

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

User · Answer

POSTGRESQL 9 1 12  DUMP   pg dump -U user db name  gt  archive name sql   put the user password and press enter   RESTORE   psql -U user db name  lt   directory archive sql   put the user password and press enter

User · Answer

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  lt  infile   if you need to set username just add the username after the command like   psql dbname  lt  infile username

User · Answer

Try to see if the following commands can help you   sudo su - yourdbuser psql  i yourbackupfile

User · Answer

Backup   gt  Option1  To take backup along  with password in cmd      1 PGPASSWORD  quot mypassword quot   pg dump -U postgres -h localhost --inserts mydb gt mydb sql Option2  To take backup without password in cmd      2  pg dump -U postgres -h localhost --inserts mydb gt mydb sql Option3  To take backup as gzip if database is huge      3  pg dump -U postgres -h localhost mydb --inserts   gzip  gt   mydb gz Restore      1  psql -h localhost -d mydb -U postgres -p 5432  lt  mydb sql

User · Answer

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

User · Answer

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  gt 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 gt  pg restore -Fc -U ngb -d ngb -p 5432  lt C  ngb 130918 backup   Please make sure that the backup file exists

User · Answer

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

User · Answer

This worked for me   pg restore --verbose --clean --no-acl --no-owner --host localhost --dbname db name --username username latest dump

User · Answer

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

User · Answer

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  lt  my old database backup   where -h host  -p port  -u login username  -d name of database

User · Answer

try   pg restore -h localhost -p 5432 -U  lt username gt  -d  lt dbname gt  -1  lt filename gt

User · Answer

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

User · Answer

Backup     pg dump -U  user-name   source db  -f  dumpfilename sql   Restore    psql -U  user-name  -d  desintation db  -f  dumpfilename sql

User · Answer

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

User · Answer

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

User · Answer

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  lt  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

User · Answer

try this   psql -U  lt username gt  -d  lt dbname gt  -f  lt filename gt  sql   Restore DB psql from  sql file

[postgresql] Restore a postgres backup file using the command line?

Examples related to postgresql

Examples related to command-line

Examples related to backup

Examples related to restore