Copying PostgreSQL database to another server

by

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.

Note: pg_dump is for backing up and psql is for restoring. So, the first command in this answer is to copy from local to remote and the second one is from remote to local. More -> https://www.postgresql.org/docs/9.6/app-pgdump.html

More Related Contents:

  1. Keep PostgreSQL from sometimes choosing a bad query plan
  2. Primary keys with Apache Spark
  3. How can I put a database under git (version control)?
  4. How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
  5. Store query result in a variable using in PL/pgSQL
  6. Can I rollback a transaction I’ve already committed? (data loss)
  7. Kill a postgresql session/connection
  8. PostgreSQL: Drop PostgreSQL database through command line [closed]
  9. Do stored procedures run in database transaction in Postgres?
  10. Import SQL dump into PostgreSQL database
  11. how to use Blob datatype in Postgres
  12. How can I solve Postgresql SCRAM authentication problem?
  13. Convert SQLITE SQL dump file to POSTGRESQL
  14. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  15. How to login and authenticate to Postgresql after a fresh install?
  16. PostgreSQL Database Log Transaction
  17. Connect to a heroku database with pgadmin
  18. How do I automatically update a timestamp in PostgreSQL
  19. Starting and populating a Postgres container in Docker
  20. Adding a new value to an existing ENUM Type
  21. How can I import a .sql file into my Heroku postgres database?
  22. Backup/Restore a dockerized PostgreSQL database
  23. How to implement a many-to-many relationship in PostgreSQL?
  24. Difference between partition key, composite key and clustering key in Cassandra?
  25. What is an ORM, how does it work, and how should I use one? [closed]
  26. Postgres could not connect to server
  27. Entity Framework core – Contains is case sensitive or case insensitive?
  28. SQL DataType – How to store a year?
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Mysql service won’t start, InnoDB init function error

Leave a Comment