CODEWITHSUNDEEP
postgresqlubuntupostgresql-9.6
Viktor Predybaylo
Viktor Predybaylo

Reputation: 57

How to move data from postgres on Windows to postgres on Linux (Ubuntu)

I have Postgres DB on machine with Windows OS, also I have virtual machine with Linux (Ubuntu). I need to move data from Windows to Linux. Thanks in advance!

Upvotes: 0

Views: 4541

Answers (2)

coderina
coderina

Reputation: 1746

I had to transfer a DB from my local machine ( Windows 11) to an Linux (Ubuntu Bionic) server, so I did the following:

  1. Dumped my DB into a backup file with extension .sql by using the following command

  • Open the terminal

  • Go to the folder where postgresql is installed in my case it was

    C:\Program Files\PostgreSQL\14\bin

  • In the terminal write

    cd C:\Program Files\PostgreSQL\14\bin

  • Type the following command, change the <db_name> and <folder_name> as required and press enter.

    pg_dump.exe -U postgres -d <db_name> -f D:\<folder_name>\<db_name>.sql

  • Enter your password and it will create the backup in the mentioned folder.

  1. Connect to the Linux Server and copy the <db_name>.sql file

    scp  D:\<folder_name>\<db_name>.sql  username@ip_address:/<destination_folder>

  1. Install postgresql on the server

    sudo apt-get install postgresql postgresql-contrib

  • Check the status

    service postgresql status

  • Create DB

    sudo su postgres
psql -U postgres -c “create database <db_name>”

  • Import the data from the dump file to this new DB.

    psql db_name < /path/db_name.sql

Upvotes: 1

YogeshR
YogeshR

Reputation: 1736

you can use below query to restore a database from one server to another 

pg_dump -C -h SourceServer -U SourceUser  SourceDB | psql -h TargetHost -U TargetUser  TargetDB

Password can be used from pgpass.conf

Upvotes: 3

Related Questions