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:So, the
search_path
needs to be explicitly set on the new database:@Erwin Brandstetter also provides more information on viewing the search_path for roles/dbs.