Arch Upgrade Postgres

I upgraded Postgres on my Arch box accidentally. Unfortunately it was a major version so I can’t get any of that data. You could do a downgrade if your package cache is intact.

sudo pacman -U file:///var/cache/pacman/pkg/postgresql-13.6-1-x86_64.pkg.tar.zst;
sudo pacman -U file:///var/cache/pacman/pkg/postgresql-libs-13.6-1-x86_64.pkg.tar.zst;

But you’ll run in to library issues because you’re on a rolling release and old versions are for chumps.

× postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
     Active: failed (Result: exit-code) since Thu 2022-07-14 19:29:36 MST; 1h 33min ago
   Main PID: 2564 (code=exited, status=127)
        CPU: 95ms

Jul 14 19:29:36 tinman systemd[1]: Starting PostgreSQL database server...
Jul 14 19:29:36 tinman postgres[2564]: /usr/bin/postgres: error while loading shared libraries: libicui18n.so.70: cannot open shared object file: No such file or directory
Jul 14 19:29:36 tinman systemd[1]: postgresql.service: Main process exited, code=exited, status=127/n/a
Jul 14 19:29:36 tinman systemd[1]: postgresql.service: Failed with result 'exit-code'.
Jul 14 19:29:36 tinman systemd[1]: Failed to start PostgreSQL database server.

Fuck that route. I’m not going down the shared library path.

I was able to leverage Docker to spin up the version of Postgres that I needed in a container. I just needed a docker-compose.yaml:

version: '3.3'
services:
  db:
    image: postgres:13.6
    environment:
      POSTGRES_PASSWORD: postgres
      PGDATA: /opt/pgsql/data
    ports:
      - '5432:5432'
    volumes:
      - /var/lib/postgres/data:/opt/pgsql/dat

And it was up and running with sudo docker-compose up:

CONTAINER ID   IMAGE           COMMAND                  CREATED          STATUS         PORTS                                       NAMES
0c95e1ee87f0   postgres:13.6   "docker-entrypoint.s…"   21 minutes ago   Up 4 seconds   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   drone-db-1

That’s cool. I was able to connect to the Postgres database using the local data and verify that there was not schema in the database.

sudo docker exec drone-db-1 psql -U drone -d personal -c "\l"
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 personal  | drone    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

And you can see that the database that I care about has nothing of value in it:

sudo docker exec drone-db-1 psql -U drone -d personal -c "\dt"
Did not find any relations.

That’s fucking cool. If it had stuff, this would be a much longer article. I can go on my merry way with upgrading and blow this data away!

sudo pacman -Syu
# Putting back all that fresh Postgres toolchain.
su
Password:
[[email protected] postgres]# su -l postgres # I just realized that the defacto root prompt is # because they don't want you to just copy root commands! 🤯
[[email protected] ~]$ initdb -D /var/lib/postgres/data
# blah blah Success. blah blah
[[email protected] ~]$ createuser --interactive
Enter name of role to add: drone
Shall the new role be a superuser? (y/n) y
[[email protected] ~]$ createdb drone
[[email protected] ~]$ exit

Yeah boy.

psql
psql (14.3)
Type "help" for help.

drone=# \dt
Did not find any relations.
drone=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 drone     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

drone=#

That’s it. You have a new database.