PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. To get started hosting your PostgreSQL database, install the PostgreSQL package on Ubuntu with the following command:
sudo apt install postgresql postgresql-client
Once PostgreSQL Server has finished installing, you should be able to see it listening for incoming connections on port 5432. This is a good way to confirm that it's up and running as expected.
ss -nlt
By default, PostgreSQL Server will start up automatically each time your system boots. If you'd like to change this behavior, you can always modify it with this command:
sudo systemctl disable postgresql
PostgreSQL Server only listens on local loopback interface 127.0.0.1 by default. If you plan to have one or more remote clients connect to your database server, you'll need to configure PostgreSQL to listen on a different network interface. To make this change, open PostgreSQL's configuration file by using nano or your preferred text editor:
sudo nano /etc/postgresql/12/main/postgresql.conf
In this file, add the following line somewhere under the "CONNECTIONS AND AUTHENTICATION" section. This will instruct PostgreSQL to listen on all network interfaces for incoming connections.
listen_addresses = '*'
Save your changes and exit the config file. Then, restart the PostgreSQL Server for the changes to take effect.
sudo systemctl restart postgresql
You should now be able to see that PostgreSQL is listening on socket 0.0.0.0:5432. You can confirm this by executing the ss command again:
ss -nlt
Next, you should add the following line to your /etc/postgresql/12/main/pg_hba.conf configuration file, which will allow incoming client connections to all databases and users. The md5 option specifies that the users must authenticate with a password.
host all all 0.0.0.0/0 md5
To add this line to your file with a single command, just execute:
sudo bash -c "echo host all all 0.0.0.0/0 md5 >> /etc/postgresql/12/main/pg_hba.conf"
Lastly, if you have UFW firewall enabled, you can open PostgreSQL Server's listening port 5432 to any incoming TCP traffic by executing the command below:
sudo ufw allow from any to any port 5432 proto tcp
Set root paswword
sudo -u postgres psql
psql#: ALTER USER postgres PASSWORD 'password';
