Set Up Postgres Database on Your Server

1.Install Postgres and Initialization

Install Postgres though apt

sudo apt-get install postgresql

Change your default login information Log into your postgres database first

sudo -u postgres psql

Ensure your terminal is start with postgres=# , otherwise login is failed. The following commands has to be under postgres internal terminal

We now modified the password for your database account. Replace 123456 with your own password.

ALTER USER postgres WITH PASSWORD '123456';

Change your login information as system user

We need to change our password in Ubuntu as well.

su root
sudo paswd -d postgres
sudo -u postgres passwd

2.Allow remote connections

At most scenario, we need to access our database remotely. However, postgres don’t allow any remote connections as default.

Modify access permission

Open /etc/postgresql/12/main/pg_hab.conf Add a row in IPV4: host all all 0.0.0.1/0 md5,this allows all remote connections with any IPs.

vi /etc/postgresql/12/main/pg_hab.conf

The file should looks like this after modified.

# TYPE DATABASE  USER    CIDR-ADDRESS     METHOD
 # "local" is for Unix domain socket connections only
 local all    all               trust
 # IPv4 local connections:
 host  all    all    127.0.0.1/32     trust
 host  all    all    0.0.0.1/0    md5
 # IPv6 local connections:
 host  all    all    ::1/128       trust

Modified the address PostgreSQL is listening

Modify file at /etc/postgresql/12/main/postgresql.conf,and change the address from localhost to all open sources. Locate the row #listen_addresses = ’localhost’and delete the ‘#’ Change the adress to listen_addresses = ‘*’ Now, using Datagrip or sth else to check if your Postgres Database works well.

Leave a Comment

Your email address will not be published.