How to copy a Postgres database within the same DB cluster?

I want to replicate a Postgres database without dumping data using pg_dump. Both source and target database are on the same machine (Test and Production server).

I would like to create a master_db_copy as exact copy of the production server master_db.

I tried from the command line:

pg_dump -vO master_db | gzip > master_db
gunzip -cV master_db.sql.gz | psql master_db_copy

Is there a better / faster way?

The fastest way to copy a complete database within the same DB cluster is CREATE DATABASE with a TEMPLATE. This just copies a whole DB on the file level.

CREATE DATABASE master_db_copy TEMPLATE master_db;

You must be superuser or owner of the template (master_db in the example) - unless it's marked as dedicated template in pg_database.datistemplate.

And the template cannot have concurrent connections to it. Dedicated template databases make sure of this by setting pg_database.datallowconn. You might use pg_terminate_backend() to close connections. See:

• How to clone a test database from a production one in one single action?

The manual:

Two useful flags exist in pg_database for each database: the columns
datistemplate and datallowconn. datistemplate can be set to
indicate that a database is intended as a template for CREATE DATABASE. If this flag is set, the database can be cloned by any user
with CREATEDB privileges; if it is not set, only superusers and the
owner of the database can clone it. If datallowconn is false, then
no new connections to that database will be allowed (but existing
sessions are not terminated simply by setting the flag false).
datistemplate = true. [...]

StackExchange Database Administrators Q#134481, answer score: 8