0

I need to restore a Postgresql dump file to get some data out of it (note never used Postgresql before)

I've tried using the command line using the following (with relevant details filled in)

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

And initially it looks like it's working, lot's of drops and alters flashing up. But then it ends and nothing's in the db. Looking through the dump file it doesn't look like it actually got to the end either.

I've also tried just opening the dump file in a SQL Editor and executing it on my db, but a short way into the file it errors with "syntax error at or near "1" on this line

--
-- Data for Name: admin_groups; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY admin_groups (ref, title, is_locked, created, updated) FROM stdin;
1   Default t   2011-05-25 15:13:46.474025  2011-05-25 15:13:46.474025
2   Root    t   2011-05-25 15:13:54.137801  2011-05-25 15:13:54.137801
\.

Any ideas?

Tim
  • 61
  • 1
  • 6

3 Answers3

3

This eventually worked for me

psql -f {dump file name} {db name}
Tim
  • 61
  • 1
  • 6
2

For restore database dump file in Postgresql use this sql command

pg_restore -p 5433 -U postgres -d databasename  -v "/var/tmp/dumpfilename.dump"

For dump data base in Postgresql use this SQL command

pg_dump -p 5433 -U postgres -F c -b -v -f "/var/tmp/dumpfilename.dump" databasename

Here port number(5433) based on installation which port number we given that port number we use.

ekostadinov
  • 6,880
  • 3
  • 29
  • 47
Nanda Thota
  • 322
  • 3
  • 10
0

I have the same problem.
This answer helped me: https://stackoverflow.com/a/15155659/18361507
When installing using the default path on Windows:

C:\Program Files\PostgreSQL\15\bin>psql -U {username} -f {path to .dump} {db name}
DGS
  • 121
  • 2
  • 3