Clone a PostgreSQL database from a VPS to local Windows
Make a full copy (schema and data) of a PostgreSQL database on a VPS onto a Windows dev machine using an SSH tunnel, pg_dump and pg_restore, including how to move the local PostgreSQL port out of the way.
On this page
- Quick reference
- 1. Find the actual PostgreSQL data directory
- 2. Change the local PostgreSQL port
- 3. Create the local database
- 4. Open the SSH tunnel
- 5. Dump the database from the VPS
- 6. Restore into the local database
- 7. Check the result
- Troubleshooting
- Role does not exist error during restore
- Set port = 5433 but nothing changed
- The log directory is missing
When you need to test against real data without touching production, the cleanest
option is to clone the PostgreSQL database from the VPS onto your dev machine. This
guide does that on Windows: open an SSH tunnel to the VPS, dump with pg_dump, then
restore with pg_restore into your local PostgreSQL - schema and data included.
Replace <username> with your SSH user on the VPS, <server-ip> with the VPS IP, and
<db_user> and <database_name> with the database user and name on the VPS.
<local_database_name> is the new database on your local machine (for example
myapp_local), and <local_password> is the password of the local postgres user.
The commands assume PostgreSQL 17 - change 17 to match your installed version.
Quick reference
Prerequisite: the local PostgreSQL listens on a different port (for example 5433) so it does not clash with the SSH tunnel (5432).
# 1. Move the local PostgreSQL to port 5433 (in postgresql.conf, remove the # and edit)
# port = 5433
# Then restart the service:
net stop PostgreSQL-17
net start PostgreSQL-17
# 2. Create the local database
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -c "CREATE DATABASE <local_database_name>;"
# 3. Open the SSH tunnel (keep this terminal open)
ssh -L 5432:localhost:5432 <username>@<server-ip> -i ~/.ssh/id_ed25519 -N
# 4. Dump from the VPS through the tunnel (in another terminal)
& "C:\Program Files\PostgreSQL\17\bin\pg_dump.exe" -h localhost -p 5432 -U <db_user> -d <database_name> -F c -f D:\db_dump.backup
# 5. Restore into the local database
& "C:\Program Files\PostgreSQL\17\bin\pg_restore.exe" -h localhost -p 5433 -U postgres -d <local_database_name> -F c D:\db_dump.backup
# 6. Check the result
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -d <local_database_name> -c "\dt"1. Find the actual PostgreSQL data directory
PostgreSQL on Windows can be installed in several places. Use the Registry to find the exact location:
reg query "HKLM\SYSTEM\CurrentControlSet\Services\PostgreSQL-17" /v ImagePathThe output shows a path similar to this:
ImagePath REG_EXPAND_SZ "C:\Program Files\PostgreSQL\17\bin\pg_ctl.exe" runservice -N "PostgreSQL-17" -D "D:\Software\PostgreSQL\17\data" -wThe -D "..." part is the data directory. In the example above it is
D:\Software\PostgreSQL\17\data - the following steps use that path, so swap in the
one from your machine.
2. Change the local PostgreSQL port
The port has to change because the SSH tunnel will occupy port 5432. Open the config file with Notepad (run it as Administrator):
notepad "D:\Software\PostgreSQL\17\data\postgresql.conf"Find the port line and edit it (make sure to remove the # if there is one):
# Before (commented out, has no effect):
#port = 5432
# After (uncommented and changed):
port = 5433Save the file and restart the service:
# Stop, then start again
net stop PostgreSQL-17
net start PostgreSQL-173. Create the local database
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -c "CREATE DATABASE <local_database_name>;"4. Open the SSH tunnel
Open a separate terminal and keep it open for the whole dump:
ssh -L 5432:localhost:5432 <username>@<server-ip> -i ~/.ssh/id_ed25519 -NFrom now on localhost:5432 on your machine is the PostgreSQL running on the VPS. For
more on tunnels (background mode, keeping the connection alive) see
Connect to PostgreSQL on a VPS through an SSH tunnel.
5. Dump the database from the VPS
Open another terminal (do not close the SSH one):
& "C:\Program Files\PostgreSQL\17\bin\pg_dump.exe" -h localhost -p 5432 -U <db_user> -d <database_name> -F c -f D:\db_dump.backup-F c: custom format (compressed, restores faster than plain SQL)-f: output file path- Enter the password for
<db_user>when prompted
6. Restore into the local database
& "C:\Program Files\PostgreSQL\17\bin\pg_restore.exe" -h localhost -p 5433 -U postgres -d <local_database_name> -F c D:\db_dump.backup7. Check the result
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -d <local_database_name> -c "\dt"This lists every table - if you get a table list, the restore worked.
Connection string to use in your app:
postgresql://postgres:<local_password>@localhost:5433/<local_database_name>Troubleshooting
Role does not exist error during restore
pg_restore: error: could not execute query: ERROR: role "<db_user>" does not exist
Command was: ALTER SCHEMA drizzle OWNER TO <db_user>;Cause: the backup contains statements that assign ownership to the <db_user> role,
but that role does not exist on your local machine.
This error is not serious - data and schema were restored successfully, ownership
simply falls to postgres. You can use the database as is.
If you want ownership to match the VPS, create the role first and restore again:
# Create a role with the same name as the database user on the VPS
& "C:\Program Files\PostgreSQL\17\bin\psql.exe" -h localhost -p 5433 -U postgres -c "CREATE ROLE <db_user> WITH LOGIN PASSWORD '<password>' SUPERUSER;"
# Restore again (--clean drops the old objects first)
& "C:\Program Files\PostgreSQL\17\bin\pg_restore.exe" -h localhost -p 5433 -U postgres -d <local_database_name> --clean -F c D:\db_dump.backupSet port = 5433 but nothing changed
Check whether the line is commented out. A line starting with # is disabled:
# No effect (commented out):
#port = 5433
# Takes effect:
port = 5433The log directory is missing
If D:\Software\PostgreSQL\17\data\log does not exist, PostgreSQL has never started
successfully. Start it manually to see the error:
& "C:\Program Files\PostgreSQL\17\bin\pg_ctl.exe" start -D "D:\Software\PostgreSQL\17\data" -l "D:\Software\PostgreSQL\17\data\startup.log"
notepad "D:\Software\PostgreSQL\17\data\startup.log"