PostgreSQL เปิดให้ remote ได้จากเครื่อง อื่นๆ

ขั้นตอนที่ 1: แก้ไขไฟล์ postgresql.conf เพื่อเปิดรับ IP 

• หาไฟล์คอนฟิก

$ sudo -u postgres psql -c "SHOW config_file;"

• แก้ไขคอนฟิกตามพาธที่ได้

$ sudo nano /etc/postgresql/*/main/postgresql.conf

• ค้นหาบรรทัด listen_address = 'localhost' ค่าเริ่มต้นจะถูก comment อยู่

listen_addresses = '*'

• บันทึกและปิดไฟล์

ขั้นตอนที่ 2: แก้ไขไฟล์ pg_hba.conf เพื่ออนุญาตสิทธิ์เข้าใช้งาน

• ค้นหาที่อยู่ไฟล์ pg_hba.conf:

$ sudo -u postgres psql -c "SHOW hba_file;"

• เปิดแก้ไข

$ sudo nano /etc/postgresql/*/main/pg_hba.conf

• เลื่อนลงไปล่างสุดของไฟล์ แล้วเพิ่มบรรทัดนี้ลงไป
• กรณีให้เข้าได้เฉพาะวง LAN เดียวกัน (ปลอดภัยกว่า):

host    all             all             192.168.100.0/24        scram-sha-256

• หรือกรณีต้องการเปิดให้ทุก IP เข้ามาได้:

host    all             all             0.0.0.0/0               scram-sha-256

• (หมายเหตุ: หากเวอร์ชันเดิมใช้รหัสผ่านแบบ md5 สามารถเปลี่ยนคำว่า scram-sha-256 ด้านหลังเป็น md5 ได้)
• บันทึกและปิดไฟล์

ขั้นตอนที่ 3: รีสตาร์ต PostgreSQL Service

$ sudo systemctl restart postgresql

$ sudo systemctl status postgresql

• ทดสอบ remote connect

$ psql -h remote_host_ip -U user1 -d mydb

เพิ่มเติม

• พอร์ตเค้าคือ 5432  TCP ตรวจสอบ Firewall ด้วย

เข้า shell PostgreSQL dump and restore

เข้า shell PostgreSQL

Linux

$ sudo -u postgres psql

Windows

> psql -U postgres

เราจะได้ shell แบบนี้

postgres=# 

ออกจาก shell 

postgres=# \q

-- สร้าง user

CREATE USER user1 WITH PASSWORD 'your_password';

-- สร้างฐานข้อมูลใหม่ชื่อ mydb

CREATE DATABASE mydb OWNER user1;

-- หรือหากมีฐานข้อมูลเดิมอยู่แล้ว ให้มอบสิทธิ์เข้าถึงทั้งหมด

GRANT ALL PRIVILEGES ON DATABASE mydb TO user1;

เข้าระบบระบุ db

$ psql -U user1 -h localhost -d mydb

Export data  จาก remote Server 1 (สั่ง dump ที่ client server 2  เลย)

$ pg_dump -U <postgres_user> -h localhost -d <dbname> -F c -b -v -f <backup_file>.dump

$ pg_dump -U user1 -h remote_host -d mydb -F c -b -b -f mydb.dump  

Restore to server 2

# สร้างฐานข้อมูลเปล่า

$ createdb -U <postgres_user> -h localhost <dbname>

# ทำการ restore ข้อมูล

$ pg_restore -U <postgres_user> -h localhost -d <dbname> -v <backup_file>.dump

$ pg_restore -U user1 -h localhost -d mydb -v mydb.dump

Ref: by Gemini