Recently, I encountered a situation where I needed to export a database from a remote server. The problem was that the storage was very limited and couldn't hold both the database itself and its exported data. It's possible to create a database dump and redirect the command output through an SSH connection to store the data on your workstation.
This procedure only works if the database host server is running Linux/UNIX.
It has not been tested in the scenario where the PostgreSQL service runs on a Windows host server (knowing that Windows does not natively offer SSH).
It has not been tested in the scenario where the PostgreSQL service runs on a Windows host server (knowing that Windows does not natively offer SSH).
Instructions for Linux/UNIX client workstations
On a machine running Linux/UNIX, simply navigate to the folder where you want to store the dump, then type the following command in a terminal:
ssh -t USER@SERVEUR "pg_dump -Fp -U postgres DATABASE" > DUMP_FILE.sqlReplace USER with a user authorized to connect to the server via SSH.
Replace SERVER with the IP address or name of the server to connect to.
Replace DUMP_FILE.sq with the name of the desired file that will be created, and which will contain the extraction.
Replace SERVER with the IP address or name of the server to connect to.
Replace DUMP_FILE.sq with the name of the desired file that will be created, and which will contain the extraction.
In our example, the postgres user is used to connect to the database, but of course you must use the one that allows you to connect to it for database server administration.
Instructions for Windows client workstations
The process is similar to performing the operation from a Linux/UNIX machine. You must type the command in a PowerShell window once you have navigated to the desired recovery folder.
