DEV Community

Cover image for Installing Postgres on a Development Machine
Talles L
Talles L

Posted on

Installing Postgres on a Development Machine

#postgres #ubuntu #linux

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
Enter fullscreen mode Exit fullscreen mode

After that apt show postgresql was giving me version 17, great. Proceeding to installation:

$ sudo apt install postgresql
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Checking Connectivity with pg_ready

There's a lovely utility for checking connectivity with Postgres:

$ pg_isready /var/run/postgresql:5432 - accepting connections
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Locate the following line:

# Database administrative login by Unix domain socket local all postgres peer
Enter fullscreen mode Exit fullscreen mode

Replace "peer" for "trust":

# Database administrative login by Unix domain socket local all postgres trust
Enter fullscreen mode Exit fullscreen mode

In the same file, locate the following line:

# "local" is for Unix domain socket connections only local all all peer
Enter fullscreen mode Exit fullscreen mode

Again, replace "peer" for "trust":

# "local" is for Unix domain socket connections only local all all trust
Enter fullscreen mode Exit fullscreen mode

Restart the service:

$ sudo systemctl restart postgresql@17-main.service
Enter fullscreen mode Exit fullscreen mode

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=#
Enter fullscreen mode Exit fullscreen mode

Run the following:

postgres=# CREATE ROLE <your-username> WITH LOGIN SUPERUSER; postgres=# CREATE DATABASE <your-username> OWNER <your-username>;
Enter fullscreen mode Exit fullscreen mode

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=#
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

Then:

$ sudo apt install pgadmin4-desktop
Enter fullscreen mode Exit fullscreen mode

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 (1)

Collapse
 
mati_brian_0e103a6fd3f89d profile image
mati brian •

thanks..ctrl +c && ctrl + v