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?

Answer 1

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.