No schema, tables after sucessful restore of PostgreSQL database

All we need is an easy explanation of the problem, so here it is.

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.

No schema, tables after sucessful restore of PostgreSQL database

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"

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply