- If your source and target database resides in the same local machine, you can use:
Note:- Sourcedb already exists in your database.
CREATE DATABASE targetdb WITH TEMPLATE sourcedb;
This statement copies the sourcedb to the targetdb.
- If your source and target databases resides on different servers, you can use following steps:
Step 1:- Dump the source database to a file.
pg_dump -U postgres -O sourcedb sourcedb.sql
Note:- Here postgres is the username so change the name accordingly.
Step 2:- Copy the dump file to the remote server.
Step 3:- Create a new database in the remote server
CREATE DATABASE targetdb;
Step 4:- Restore the dump file on the remote server
psql -U postgres -d targetdb -f sourcedb.sql
(pg_dump is a standalone application (i.e., something you run in a shell/command-line) and not an Postgres/SQL command.)
This should do it.