Load database dump into postgres database

Question

I have a database dump about 270MB which I want to load into my local postgres database. I typed in the following command:

pg_dump databasename < dumpfile.sql

After which I get:

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: public; Type: ACL; Schema: -; Owner: starlord
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM starlord;
GRANT ALL ON SCHEMA public TO starlord;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

To verify if the dump was successful and to check the data I logged into the psql terminal using psql which opens the psql terminal.

If I type \l I get all the databases. I typed \c databasename to connect to the database and then \dt to check the tables. However it keeps showing me No relations found. The same steps work totally fine for other databases ( loading a dump, and then connecting and displaying tables). Moreover after loading the dump if I type \l+ to see the size of each database, the database I am interested in shows a size of 6973kb where as the dump is 272MB.

Any suggestions as to what could be the issue?

To clarify, did you try to load the database with pg_dump databasename < dumpfile.sql? If so, that's your problem. pg_dump is for dumping, not loading. — Schwern
– Schwern, Commented Mar 22, 2016 at 22:51
Your redirection looks to be backwards - you're piping data into pg_dump, which is a tool for getting data out of the database. If you already have an SQL-format backup, you need to pipe it into psql, to actually execute the commands in the file. — IMSoP
– IMSoP, Commented Mar 22, 2016 at 22:51

Schwern · Accepted Answer · 2016-03-22 22:53:40Z

7

pg_dump is for dumping the database. pg_dump databasename < dumpfile.sql will probably ignore the input file and spew the dump to the screen, which seems to be what you got.

To restore from a dumpfile, pipe it into the Postgres command line tool psql.

psql dbname < dumpfile.sql

See also the Postgres docs on backup and restore.

answered Mar 22, 2016 at 22:53

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rakesh Bakoriya · Accepted Answer · 2024-04-29 17:59:58Z

0

Restore latest.dump file to Local Database

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

answered Apr 29, 2024 at 17:59

Rakesh Bakoriya

2212 silver badges6 bronze badges

Collectives™ on Stack Overflow

Load database dump into postgres database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related