How do I backup a Postgres database from remote server A to a Linode server

Question

I have a postgres database currently deployed in a virtual server. I want to backup this database and exclude some tables and restore it in a Linode server. My current thought is to dump it in the local machine and restore it to the Linode server.

My manual backup command

$ pg_dump -T analytics_* -T aggregated* -T completeness* -O -x database_name | gzip > name_backup_.sql.gz

In this command, analytics_, aggregated*, completeness are tables I want to exclude.

My question is,

1. Should I backup in the remote server or my local machine?

2. If I backup in any of the suggestion above:

   a. How do I write the command above?

   b. How do I write the command to restore the database in Linode?

Answer 1

Depends how big a database ?

1. For small database where you have access to VirtalServer from your linode server, i'd just pipe it to psql pg_dump -h <virtualserverIP> -U <username> | psql

2. For larger then pg_dump on the virualserver, scp ftp S3 copy etc.. to linode then psql on the linode box

3. For largest then i'd pg_dump -Fd & pg_restore