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?
pg_dump databasename < dumpfile.sql? If so, that's your problem.pg_dumpis for dumping, not loading.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 intopsql, to actually execute the commands in the file.