First Try...

After having done the configuration to allow remote connections from the local network to PostgreSQL so i could development from different machines, but still using the same database so to avoid duplication of data as well duplication of PostgreSQL instances.

Now what we want is to create a copy of the current live/production database and add a "dev" prefix to it. This can also be used to create a backup of the production database in the same PostgreSQL instance, if for example we want to make some changes to the database and in case something goes wrong you will have the backup.

PostgreSQL has some ways of doing this by just using internal commands and tools and i will stick with the simple approach where PostgreSQL allows you to create a copy of an database using "CREATE DATABASE" statement with the "TEMPLATE" option:

CREATE DATABASE backup_db WITH TEMPLATE original_db OWNER role_name;

The above statement creates a "backup_db" database that is a copy of the "original_db" and we can add an extra option, OWNER to set the owner of the new copy. If no OWNER is set, then the owner will be assigned to the user running the command.

root@machine:/root # su postgres
$ psql
psql (16.11)
Type "help" for help.

postgres=# 
postgres=# create database test_dev with template test owner testuser;
ERROR:  source database "test" is being accessed by other users
DETAIL:  There is 1 other session using the database.

The error i got is of course related to the fact that when you want to copy a databa, you should have no active connections to it, or it i will fail with the above error.
We can check the active connections, and of course we can also from the server close connections.

First we check the active connections with:

postgres=# select pid, usename, client_addr from pg_stat_activity where datname = 'test';
  pid  | usename  | client_addr 
-------+----------+-------------
 65120 | testuser | 127.0.0.1
(1 row)

postgres=# 

And then we can close the active connections with:

postgres=# select pg_terminate_backend(65120) from pg_stat_activity where datname = 'test';
 pg_terminate_backend 
----------------------
 t
(1 row)

postgres=# 

Now with the connections closed we can go ahead and do the copy:

postgres=# create database test_dev with template test owner testuser;
CREATE DATABASE
postgres=# \c test_dev
You are now connected to database "test_dev" as user "postgres".
test_dev=# \d
                   List of relations
 Schema |           Name           |   Type   |  Owner  
--------+--------------------------+----------+---------
 public | table1                   | table    | testuser
 public | table1_id_seq            | sequence | testuser
 public | table2                   | table    | testuser
 public | table2_id_seq            | sequence | testuser
 public | users                    | table    | testuser
 public | users_id_seq             | sequence | testuser
(8 rows)

We also connected to the newly created database and just listed the tables present.

Now we have a copy of the production databse, and we can just go to our development environments and configure it so it uses the new copy for development.