PostgreSQL – CREATE DATABASE WITH TEMPLATE Losing Relations

copypostgresql

I'm trying to Create a copy of a database in postgresql per the SO answer from 2009, but running into problems.

In Postgres 9.3.9, this creates a database with no relations (the gcis db exists and has tables and data):

postgres=# CREATE DATABASE gcis_rollback WITH TEMPLATE gcis OWNER postgres;
CREATE DATABASE
postgres=# \c gcis_rollback
You are now connected to database "gcis_rollback" as user "postgres".
gcis_rollback=# \d
No relations found.

I get the same using the commandline createdb:

~$ createdb -O postgres -T gcis gcis_rollback2
~$ psql gcis_rollback2
psql (9.3.9)
Type "help" for help.

gcis_rollback2=# \d
No relations found.

Why don't I see a full copy of this DB?

Background – This is a dev server, where I can take down the connections to make a copy. What I want is just a local copy for ease of rollback purposes while developing/testing DB schema changes using the Perl framework Module::Build::Database to build a patch.

Additional info:

gcis=# \l+ gcis
                                               List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description 
------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------
 gcis | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 35 MB | pg_default | 


gcis=# \d
                        List of relations
    Schema     |             Name             |   Type   | Owner  
---------------+------------------------------+----------+--------
 gcis_metadata | _report_editor               | table    | ubuntu
...
(57 rows)

Best Answer

Thanks to @a_horse_with_no_name for leading me to this answer.

The relations appear to be missing because Postgres does not copy the search_path to the new database. If you explicitly look in the specific schema, all relations are there:

\dt <schema>.*

So, the search_path needs to be explicitly set on the new database:

ALTER DATABASE gcis_rollback2 SET SEARCH_PATH gcis_metadata, public

@Erwin Brandstetter also provides more information on viewing the search_path for roles/dbs.