Install PostgreSQL on Ubuntu 20.04 LTS

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';
 
 

0 Komentar