How to Install and Configure PostgreSQL on Ubuntu 20.04

How to Install and Configure PostgreSQL on Ubuntu 20.04

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:

  • Create the file repository configuration.

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'        

  • Import the repository signing key.

wget - quiet -O https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add        

  • Update the package lists.

sudo apt-get update        

  • Install the latest version of PostgreSQL. If you want a specific version, use ‘postgresql-12’ or something similar instead of ‘postgresql’.

sudo apt-get -y install postgresql        

  • Verify installation.

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        

Setting parameters in PostgreSQL:

sudo vim /etc/postgresql/15/main/postgresql.conf        

Client authentication configuration file pg_hba.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.

  • Change the listening address in the postgresql.conf file.

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.

  • Add a client authentication entry to the pg_hba.conf file.

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.

  • Configuration manager

The PGTune website calculates the configuration for PostgreSQL based on the maximum performance for a given hardware configuration.


  • Disaster Recovery

Barman allows for the implementation of disaster recovery solutions for PostgreSQL databases with high requirements for business continuity.


  • Cluster managers

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.


  • PostgreSQL security

Postgres adds transparent data encryption, additional advanced security, and new Oracle compatibility features.

https://www.enterprisedb.com/

要查看或添加评论,请登录

Andrey Byhalenko的更多文章

社区洞察

其他会员也浏览了