How to Install and Configure PostgreSQL on Ubuntu 20.04
Andrey Byhalenko
I am a self-driven DevOps expert with 15 years of experience in the IT industry. I write articles aimed at Junior DevOps engineers and those aspiring to become DevOps engineers.
In this tutorial, I will show how to install PostgreSQL from the apt repository and go over the basic configurations.
Installation:
To use the apt repository, follow these steps:
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget - quiet -O https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add
sudo apt-get update
sudo apt-get -y install postgresql
sudo systemctl status postgresql
Using PostgreSQL Roles and Databases:
The concept of “roles” is how Postgres handles authorization and authentication by default. These resemble typical Unix-style users and groups in certain aspects.
Postgres is configured to employ ident authentication by default, which links Postgres roles to corresponding Unix/Linux system accounts. A Unix/Linux username with the same name can log in as that role if it exists within Postgres.
During the installation process, a user account called postgres was created, and it is by default linked to the postgres role. You can use this account to access Postgres in a few different ways. You can use the following command to change your server’s account to Postgres:
sudo -i -u postgres
Then you can access the Postgres prompt by running:
psql
This will launch the PostgreSQL prompt, where you can immediately begin interacting with the database management system. Use these commands to close the PostgreSQL prompt:
\q
This will bring you back to the Postgres Linux command prompt. To return to your regular system user, run the exit command:
exit
Another way to connect to the Postgres prompt is to run the psql command as the Postgres account directly with sudo:
sudo -u postgres psql
This will log you directly into Postgres without the intermediary bash shell in between. Again, you can exit the interactive Postgres session by executing the exit command:
exit
Creating a New Role.
If you are logged in as the postgres account, you can create a new role by running the following command:
createuser --interactive
If you prefer to use sudo for each command without switching from your normal account, run:
sudo -u postgres createuser --interactive
In either case, the script will present you with a few options and then, in response to your selections, will run the appropriate Postgres commands to create a user according to your preferences.
OutputEnter name of role to add: danny
Shall the new role be a superuser? (y/n) y
Creating a New Database.
Another default assumption made by the Postgres authentication system is that any role used to log in would be able to access a database with the same name.
This implies that the role will try to connect to a database, which is likewise named “danny” by default, if the user you created in the previous section is called Danny. The createdb command can be used to construct the necessary database.
You would type something like this if you were logged into the Postgres account:
createdb danny
If you prefer to use sudo for each command without switching from your normal account, you would run
sudo -u postgres created danny
领英推荐
Opening a Postgres Prompt with the New Role.
To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.
If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the Postgres user):
sudo adduser danny
Once this new account is available, you can either switch over or connect to the database by running the following:
sudo -i -u danny
psql
Or, you can do this inline:
sudo -u danny psql
Assuming that every component has been set up correctly, this command will immediately log you in.
You can define the database in the following way if you want your user to connect to a different database:
psql -d postgres
Once logged in, you can check your current connection information by running:
\conninfo
Output////
You are connected to database "danny" as user "danny" via socket in "/var/run/postgresql" at port "5432".
Verify that port 5432 is open to accept connections.
netstat -pln | grep 5432
sudo vim /etc/postgresql/15/main/postgresql.conf
sudo vim /etc/postgresql/15/main/pg_hba.conf
Connecting to a Remote PostgreSQL Database.
If your PostgreSQL database is installed on a separate server, you need to change the default settings in the postgresql.conf and pg_hba.conf files in the remote database.
By default, PostgreSQL allows you to listen for the localhost connection. It does not allow a remote TCP/IP connection. To allow a remote TCP/IP connection, add the following entry to the /etc/postgresql/15/main/postgresql.conf file:
listen_addresses = '*.*.*.*'
If you have multiple interfaces on the server, you can specify a specific interface to be listened to.
By default, PostgreSQL accepts connections only from the local host. It refuses remote connections. This is controlled by applying an access control rule that allows a user to log in from an IP address after providing a valid password (the md5 keyword). To accept a remote connection, add the following entry to the /etc/postgresql/15/main/pg_hba.conf file:
host all all *.*.*.*/* md5
For example,192.168.104.24/26 trust.
Best Practices and Maintenance.
The PGTune website calculates the configuration for PostgreSQL based on the maximum performance for a given hardware configuration.
Barman allows for the implementation of disaster recovery solutions for PostgreSQL databases with high requirements for business continuity.
repmgr is an open-source tool suite for managing replication and failover in a cluster of PostgreSQL servers.
Patroni is a cluster manager used to customize and automate the deployment and maintenance of PostgreSQL HA (high availability) clusters.
Postgres adds transparent data encryption, additional advanced security, and new Oracle compatibility features.