Creating a copy of a database in Postgres

Question

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

Answer 1

Don't know about pgAdmin, but pgdump gives you a dump of the database in SQL. You only need to create a database by the same name and do 'psql mydatabase < mydump' to restore all of the tables and their data and all access privileges.

Answer 2

Postgres allows the use of any existing database on the server as a template when creating a new database. I'm not sure whether pgAdmin gives you the option on the create database dialog but you should be able to execute the following in a query window if it doesn't:

CREATE DATABASE newdb WITH TEMPLATE originaldb;

Answer 3

In pgAdmin you can make a backup from your original database, and then just create a new database and restore from the backup just created.

Regards