Sure, it's quicker and easier to run as a container, I know. But some people sometimes just want to have it the old way.
I'm on Pop!_OS 22.04 by the way.
Adding the APT repository and Installing It
At time of writing, apt show postgresql tells me that version 14 will be installed while I can see version 17 on postresql.org homepage.
Thankfully there are some clear instructions on the website how to set up postgresql.org APT repo on your system. I went the lazy route:
$ sudo apt install -y postgresql-common
$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
After that apt show postgresql was giving me version 17, great. Proceeding to installation:
$ sudo apt install postgresql
The Systemd Service
After installation was finished I got postgresql up and running as service, without any configuration of my own:
$ systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2024-11-29 23:03:31 -03; 1min 2s ago
Main PID: 17315 (code=exited, status=0/SUCCESS)
CPU: 1ms
Nov 29 23:03:31 pop-os systemd[1]: Starting PostgreSQL RDBMS...
Nov 29 23:03:31 pop-os systemd[1]: Finished PostgreSQL RDBMS.
Out of curiosity, let's check the .service file:
$ cat /lib/systemd/system/postgresql.service
# postgresql.service is the meta unit for managing all PostgreSQL clusters on
# the system at once. Conceptually, this unit is more like a systemd target,
# but we are using a service since targets cannot be reloaded.
#
# The unit actually managing PostgreSQL clusters is postgresql@.service,
# instantiated as postgresql@15-main.service for individual clusters.
[Unit]
Description=PostgreSQL RDBMS
[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on
[Install]
WantedBy=multi-user.target
Wait a second, that's just a dummy service pointing to /bin/true.
Fiddling a bit I figure it out that there was another service, postgresql@17-main.service:
$ systemctl status postgresql@17-main.service
● postgresql@17-main.service - PostgreSQL Cluster 17-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
Active: active (running) since Fri 2024-11-29 23:03:35 -03; 4min 20s ago
Process: 18149 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 17-main start (code=exited, status=0/SUCCESS)
Main PID: 18154 (postgres)
Tasks: 6 (limit: 38324)
Memory: 21.3M
CPU: 267ms
CGroup: /system.slice/system-postgresql.slice/postgresql@17-main.service
├─18154 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf
├─18155 "postgres: 17/main: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
├─18156 "postgres: 17/main: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >
├─18158 "postgres: 17/main: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
├─18159 "postgres: 17/main: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
└─18160 "postgres: 17/main: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
Nov 29 23:03:32 pop-os systemd[1]: Starting PostgreSQL Cluster 17-main...
Nov 29 23:03:35 pop-os systemd[1]: Started PostgreSQL Cluster 17-main.
Checking Connectivity with pg_ready
There's a lovely utility for checking connectivity with Postgres:
$ pg_isready
/var/run/postgresql:5432 - accepting connections
Enabling Trust Authentication
This is a development machine and Postgres got us covered with what they call trust authentication. No need to remember usernames and password? I'm in.
To enable it, will change the following file:
$ sudo vim /etc/postgresql/17/main/pg_hba.conf
Locate the following line:
# Database administrative login by Unix domain socket
local all postgres peer
Replace "peer" for "trust":
# Database administrative login by Unix domain socket
local all postgres trust
In the same file, locate the following line:
# "local" is for Unix domain socket connections only
local all all peer
Again, replace "peer" for "trust":
# "local" is for Unix domain socket connections only
local all all trust
Restart the service:
$ sudo systemctl restart postgresql@17-main.service
Logging into the database
Let's get into the database with the "postgres" user:
$ psql --username=postgres
psql (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
Type "help" for help.
postgres=#
Run the following:
postgres=# CREATE ROLE <your-username> WITH LOGIN SUPERUSER;
postgres=# CREATE DATABASE <your-username> OWNER <your-username>;
Ctrl+D back to your shell. You should be able to get into the database by just calling psql with no arguments now:
$ psql
psql (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
Type "help" for help.
talles=#
Installing pgAdmin
We have to add a new repository:
$ curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Then:
$ sudo apt install pgadmin4-desktop
Installation is done. Open up pgAdmin and head to the "register server" dialog. To use local authentication, put /var/run/postgresql in the hostname field:
(credits to this Stack Overflow answer for this trick)
You should be in :)
Top comments (0)