PostgreSQL how to create a copy of a database or schema?

pg_dump with the --schema-only option.

This can be done by running the following command:

CREATE DATABASE [Database to create] WITH TEMPLATE [Database to copy] OWNER [Your username];

Once filled in with your database names and your username, this will create a copy of the specified database. This will work as long as there are no other active connections to the database you wish to copy. If there are other active connections you can temporarily terminate the connections by using this command first:

SELECT pg_terminate_backend(pg_stat_activity.pid) 
FROM pg_stat_activity 
WHERE pg_stat_activity.datname = '[Database to copy]' 
AND pid <> pg_backend_pid();

A good article that I wrote for Chartio's Data School which goes a bit more in depth on how to do this can be found here: https://dataschool.com/learn/how-to-create-a-copy-of-a-database-in-postgresql-using-psql

If it's on the same server, you just use the CREATE DATABASE command with the TEMPLATE parameter. For example:

CREATE DATABASE newdb WITH TEMPLATE olddb;

If you have to copy the schema from the local database to a remote database, you may use one of the following two options.

Option A

1. Copy the schema from the local database to a dump file.

   pg_dump -U postgres -Cs database > dump_file
   

2. Copy the dump file from the local server to the remote server.

   scp localuser@localhost:dump_file remoteuser@remotehost:dump_file
   

3. Connect to the remote server.

   ssh remoteuser@remotehost
   

4. Copy the schema from the dump file to the remote database.

   psql -U postgres database < dump_file
   

Option B

Copy the schema directly from the local database to the remote database without using an intermediate file.

pg_dump -h localhost -U postgres -Cs database | psql -h remotehost -U postgres database

This blog post might prove helpful for you if you want to learn more about options for copying the database using pg_dump.