Creating a copy of the database in PostgreSQL

Question

I'm trying to create the first copy of my database. I'm using PostgreSQL and Ubuntu 16+ with Django technology.

I found this documentation to create a copy:

I'm trying to export the entire database to a file so that I can add it to another server. I tried this:

pg_dump app_prod > test_copy
pg_dump --host=localhost --username=app --dbname=app_prod --file=testdb.sql

after selecting ls my directory can see the database. But by running eg WinSCP it is not visible.

How can I take these files, copy them to my Windows system and upload to another Ubuntu server?

I think that it is enough to make them visible in WinSCP. How can I do this?

EDIT:

drwxr-xr-x 3 postgres postgres    4096 Oct  4 08:06 9.5
-rw-rw-r-- 1 postgres postgres 3578964 Jan 18 10:46 test_copy
-rw-rw-r-- 1 postgres postgres       0 Jan 18 10:54 testdb.sql

Answer 1

It seems like this was resolved in the comments: you were looking at the wrong folder in the WinSCP folder explorer.

There are a few items worth noting to bolster the good advice already given:

Your ls -l output indicates that the SQL file is zero bytes in size, so something has gone wrong there. If you manage to transfer it to your local machine, you will find it is empty.

Also, try not to store database dumps in /var/lib/postgresql - this is where your PostgreSQL database keeps live database files on the server, and you don't want to risk changing or deleting anything here. Use /home/maddie instead (change the username as appropriate).