Tuesday, 25 March 2014

PostgreSQL - pg_dump to copy PostgreSQL Database From One Remote Server to Another

While involved in Python + Django based projects and using postgreSQL as a database backend, there are occasions where I need to switch between servers and at times need to copy databases as well. For transferring the django application data + schema from one remote server to another, I use following ways as per convenience, available options, server restrictions etc.

1) Copying Django application data + schema (PostgreSQL) from one remote server by dumping the data in a file and then transferring that file to another remote server and restoring it. In this approach, you have got three options:

  • Dump both schema as well as Data to the file:
pg_dump -C -h host -U username db_name > /any_directory/dump_schema_and_data_file 
  • Dump only schema from PostgreSQL Database to a file:
pg_dump -Cs -h host -U username db_name > /any_directory/dump_schema_file
  • Dump only data from PostgreSQL Database to file:
pg_dump -a -h host -U username db_name > /any_directory/dump_data_file

After copying the above file to target server, you can easily restore database from file by using following command:
psql -h host -U username db_name < data_schema_file

2) Second approach I use to copy PostgreSQL database from one remote server to another is by directly using following command i.e without copying the data to intermediate file:

pg_dump -C -h localhost -U localuser db_name | psql remotehost -U remoteuser db_name


  1. Replies

    1. Ở xung quanh bắt đầu rung chuyển sóng gợn, mây đen bắt đầu vặn vẹo.

      Ở trên không trung điện quang bắt đầu ngưng tụ, gió lạnh thổ qua, một mảng mưa băng bắt đầu thành hình.

      Long quyền phong bạo xuất hiện, ngọn lửa nóng chảy, còn có khí tức tràn ngập, cự thạch cũng ngập trời.
      game mu
      cho thue phong tro
      nhac san cuc manh
      tổng đài tư vấn luật
      văn phòng luật
      tổng đài tư vấn pháp luật
      thành lập công ty
      chém gió
      trung tâm ngoại ngữ
      Một lực lượng cường hãn bổ lên trên người của Nhạc Thiên lão tổ và Nhạc Thành, khiến cho cả hai cảm thấy bình thường.

      - Bày ra phòng ngự.

      Nhạc Thiên lão tổ cất tiếng nói, một mảng đấu khí bắt đầu ngưng tụ, hai tay xẹt qua trên không trung, một vòng tròn đấu khí phòng ngự bắt đầu bao trùm, Nhạc Thành cũng rót một luồng Kim Long đấu khí vào.

  2. The next thing you must follow is that your database lists first row must contain the heading of the list. That is the first row contains your field names.mysql dashboards

  3. Besides this, the Mobile Application Development Company should also comply with the guidelines laid down by the various mobile operating systems. And make sure that the app works within these parameters.
    Edknt Media

  4. Creating a website can always be a difficult task, especially if you haven't got much experience with it. The first step is you should save your accounts with online password generator. Visit this site because they will help you with any of the issues you may face.

  5. mini golf king hack I am a BIG FAN of getting up at 5AM. It's perfect for quiet time, CREATIVE time and connection time. I hope to make videos as GREAT as yours in the near future. (I just created my first 7 day EPIC LIFE challenge

  6. After a year, analysing all kinds of diets, finally understood that we should eat normal foods that are grown locally from our native home town... no packed or imported foods. This is the trick but nobody ill like this comment!! factory inc hack

  7. You are the smartest, you are much louder and I hope you give me a heart zero city zombie shelter survival hack

  8. it's working fine for me! Thank you for sharing this with us dominations hack

  9. Hey everyone I am so excited about this tutorial because it has helped me to I really appreciate thanks for sharing horse riding tales hack