# More

Each of the following solutions has been proven to be effective and we hope to be helpful to you.

# Set remote connection

If you want to use customer tool (e.g Navicat/pgAdmin) on your local computer to connect PostgreSQL Server, you should set your remote connection first.

The database is a high-security application, set up remote access, at least two independent steps:

# Enable TCP:5432 port on your Cloud Platform

PostgreSQL is used 5432 port by default, you should login Cloud Platform to enabled this port

# Modify the PostgreSQL configuration files

Then, you should set your PostgreSQL configuration files so that PostgreSQL can be accessed by external networks

  1. Modify the configuration file postgresql.conf

    #listen_addresses = 'localhost'
    
    is set to
    
    listen_addresses = '*'
    
  2. Modify the configuration file pg_hba.conf , add the following line at the end of it

    host    all             all             0.0.0.0/0            md5
    
  3. Restart PostgreSQL

    systemctl restart postgresql
    

# Password Management

PostgreSQL customer tool can connect PostgreSQL server by Unix socket directly, so you can modify your password without authentication

# change to user postgres
sudo -u postgres psql

# modify your password
ALTER USER postgres WITH PASSWORD 'postgres';

#exit psql
\q

# Change PGDATA directory

  1. Backup all files on you PGDATA /data/postgresql/pgdata

  2. Change the PGDATA directory by different Linux distribution

    • RedHat/CentOS: modify the file postgreql.service
      # cat postgresql.service directory
      systemctl cat postgreql.service 
      
      # modify the  Environment=PGDATA in the file postgresql.servce
      Environment=PGDATA=/var/lib/pgsql/11/data/
      
    • Ubuntu: modify the file postgresql.conf
      data_directory =
      
  3. Restore all data from your backup

  4. Restart your PostgreSQL service