asked on

How the restore postgres database on linux?

Hi Experts,

I want to restore postgres database.

I had taken backup with pg_dump. the command which I used to take backup is

pg_dump -U postgres -h 127.0.0.1 -p 5432 ts > ts.tar
password:

it created the file ts.tar

Steps I had tried to restore is

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host localhost --port 5432 --username "postgres" --dbname "ts" --role "ts" --no-password --verbose "./ts.tar"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host localhost --port 5432 --username "postgres" --dbname "ts" --role "ts" --no-password < "./ts.tar"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host 127.0.0.1 --port 5432 --username "postgres" --dbname "ts" --role "ts" --no-password < "./ts.tar"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host 127.0.0.1 --port 5432 --username "postgres" --dbname "ts" --role "ts"  < "./ts.tar"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

root@ip-10-252-14-11:/home/ubuntu/workarea/sourcecode/database_backup# pg_restore --host 127.0.0.1 --port 5432 --username "ts" --dbname "ts" --role "ts"  < "./ts.tar"  pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

Open in new window

I am note able to restore the database. Please help me in restoring the database.

with many thanks,
Bharath AK

SOLUTION

Martyn Spencer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Prabhin MP

can u try these,

pg_restore -c -i -U postgres -d client03 -v "/tmp/client03.tar" -W

Essentially...

-c to clean the database
-i to ignore any database version checks
-U to force a user
-d to select the database
-v verbose mode, don't know why
"$$" the location of the files to import in tmp to get around permission issues
-W to force asking for the password to the user (postgres)

ASKER CERTIFIED SOLUTION

Bernard Savonet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

coder

ASKER

Hi All,

Steps I had done are

CREATE DATABASE ts;
CREATE USER ts WITH ENCRYPTED PASSWORD 'test';
GRANT ALL PRIVILEGES ON DATABASE ts TO ts;

su postgres

psql -d ts < ts.tar > /dev/null

Now it is working fine.

Thanks you all for your help and suggestion.

With Kind Regards,
Bharath AK

Martyn Spencer

Glad to have helped.

Bernard Savonet

glad we could help!