I backed up the stack exchange archive database hosted on an RDS instance of PostgreSQL like so:
pg_dump stackexchange -h <host> -U postgres -password -o > stack.dmp
...A couple of hours later, I have a nice 151GB dump file.
Next, I restored this puppy to a different instance:
psql --set ON_ERROR_STOP=on -h <otherhost> -U postgres -d stackexchange -f stack.dmp
...and an hour or so later it completed with no errors. Happiness.
Weird thing is, pgadmin4 doesn't show the schema that should have been created, nor any of the tables, views, etc. Connecting with psql and issuing \dn returns only the public schema.
For giggles, I looked at the size of the database, and it looks right(ish):
stackexchange=> SELECT pg_size_pretty( pg_database_size('stackexchange') ); pg_size_pretty ---------------- 145 GB (1 row)
I used head to grab the first 50-ish rows from the dump, and all looks fine:
-- -- PostgreSQL database dump -- -- Dumped from database version 11.10 -- Dumped by pg_dump version 11.11 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; -- -- Name: stackoverflow; Type: SCHEMA; Schema: -; Owner: postgres -- CREATE SCHEMA stackoverflow; ALTER SCHEMA stackoverflow OWNER TO postgres; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: posthistory; Type: TABLE; Schema: stackoverflow; Owner: postgres -- CREATE TABLE stackoverflow.posthistory ( id integer NOT NULL, posthistorytypeid integer, postid integer, revisionguid text, creationdate timestamp without time zone NOT NULL, userid integer, posttext text, jsonfield jsonb ); ALTER TABLE stackoverflow.posthistory OWNER TO postgres;
So I'm sorta stumped. All this stuff executed correctly. Database is about the right size...but no objects.
Next, I got curious and tried to recreate the schema ("stackoverflow") that SHOULD have already been created:
stackexchange=> create schema stackoverflow;
It's 5 minutes later and I'm getting no response in the console. psql is just sitting there.
Does this sound at all familiar to anyone? I'm puzzled. Ideas on next steps?
EDIT: I did a CTRL-BREAK on psql. Results:
stackexchange=> create schema stackoverflow; ^CCancel request sent ERROR: canceling statement due to user request CONTEXT: while inserting index tuple (0,12) in relation "pg_namespace_nspname_index"