0 like 0 dislike
222 views
asked in PostgreSQL Database Forum by (13.4k points)  
reshown by

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (13.4k points)  
reshown by

Postgres DB migration local server to remote server:
You don't need to create an intermediate file. You can do

./pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname
or
./pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

using psql or pg_dump to connect to a remote host.

With a big database or a slow connection, dumping a file and transfering the file compressed may be faster.

As Kornel said there is no need to dump to a intermediate file, if you want to work compressed you can use a compressed tunnel

./pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"
or
./pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"

but this solution also requires to get a session in both ends.

Postgres cluster migration local server to remote server:
1.Taking backup from local server

$ ./pg_dumpall -U postgres -W -f /localserver/all.sql

2. Download the backup by using WINSCP else some thirdparty tools
3. Upload the file to destination server means remote server
4. Restore it by using below command 

$ ./pg_restore -U postgres -d postgres </remoteserver/all.sql

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
0 answers
...