2

I have a website with Postgres DB. Site was created in 2017, So I am updating it to latest versions of all technologies. I have created another server and plan to move this entire site on new server, but I need to test each component. My site code I can directly upload from my computer to new server.

However is there a way to take dump of Postgres directly to new server? I want to take dump of Postgres directly to new server because current server has only 50 GB space and my DB is of 35GB, so backup will fail due to space constraint.

Will this code work? I do not want to take any risk with live database.

pg_dump -C -h local -U localuser source_database | psql -h remote -U remoteuser target_database

Got this from internet. So my purpose in short is: Take backup of database to restore in another server. But do not save file in old server, transfer to new server. Sorry if I sound idiotic. Have learnt 80% of things from SO.

Sapna Sharma
  • 722
  • 1
  • 8
  • 19

1 Answers1

3

I played with pg-dump a while ago and been using it for updating my test environment. Take a look at the question I asked.

The command worked just fine for me and did not cause any issues to the production (origin) db.

Here is the command I'm running in the new server.

pg_dump -h remotehost -p 5432 -U remoteuser remotedb | psql -h localhost -p 5432 -U localuser localdb

Note that I did not add the -C here, since I already had the destination db created.

rodolfo_r
  • 481
  • 6
  • 14
  • It would be great if you can give exact command I need to run on new server to pull dump from old server. I need dump.sql file and not directly restore database. – Sapna Sharma Feb 24 '21 at 18:17
  • Just added the command I use to the answer... I've done this command a number of times now and it didn't cause any harm to my production application. It only made changes to the destination db, which in my case is `-h localhost -p 5432 -U localuser localdb`... However, I don't get why you need the actual dump.sql file... isn't your new server running postgres? – rodolfo_r Feb 24 '21 at 18:28
  • This command will save the file in new server? Will it save localdb.sql dump file or try to restore the database? – Sapna Sharma Feb 24 '21 at 18:32
  • According to the docs "pg_dump -- extract a PostgreSQL database into a script file or other archive file". [Docs](https://www.postgresql.org/docs/9.1/app-pgdump.html). The command I posted in my answer, once executed, runs a script in the destination db, setting it up, creating tables, view etc. having the original db as the model and filling it with all information you had in the original one. – rodolfo_r Feb 24 '21 at 18:40