3

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)
3

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.

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.