Install and Setting PostgreSQL in Ubuntu22.04

As a memo, I will compile the steps for installing PostgreSQL on Ubuntu Desktop for development.

Install PostgreSQL

Install according to the instructions on the official website.

# 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 similar instead of 'postgresql':
$ sudo apt-get -y install postgresql

Configure to start at PC boot and initiate PostgreSQL.

$ sudo systemctl enable postgresql
$ sudo systemctl start postgresql

Check Installation and Version

$ psql -V

Create password and enable password login

When you install PostgreSQL on Ubuntu, a 'postgres' user is created. Right after installing PostgreSQL, it uses peer authentication for connections from the host, which means you cannot connect as a database user unless your Ubuntu username matches that database user.
You can connect to database as postgres user with below command and set password "postgres" to postgres user.

$ sudo -u postgres psql

postgres=# alter role postgres with password 'postgres';

Change Peer auth to Password auth for all users with rewite conf.

$ sudo vi /etc/postgresql/16/main/pg_hba.conf

# local   all             postgres                                peer
local   all             all                                password

$ sudo systemctl restart postgresql

Now you can login with below command using password.

$ psql -U postgres