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') );
 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.

enter image description here

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"

The pg_dump command you show cannot be the one you used, because there is no -o option, and assword is not a valid port number.

Anyway, your problem is a different one: there is an open transaction that holds a lock on pg_namespace that prevents you from creating a schema.

Debug as follows:

  • In a psql session, run

    SELECT pg_backend_pid();  -- to figure out your process ID
    \i stack.dmp
  • When the first session hangs, start a second one and run

    SELECT pg_blocking_pids(12345);  -- use the pid of the blocked session

Now you know which sessions are blocking you. Look at pg_stat_activity to learn more about these transactions. If necessary, terminate them with pg_terminate_backend.

In the case where the culprit is a prepared transaction, look for entries in pg_prepared_xacts and undo them with ROLLBACK PREPARED.

  • Thanks. This nudge got me going in the right direction. I was able to take a HEAD of the DDL at the top of the dump and execute that by itself. Finished without error and objects actually showed up. After that, ran the original dump, ignored "objects already exist" errors, and the various COPY blocks worked just fine. – Russell Christopher Jun 10 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.