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:
[root@tinman postgres]# su -l postgres # I just realized that the defacto root prompt is # because they don't want you to just copy root commands! 🤯
[postgres@tinman ~]$ initdb -D /var/lib/postgres/data
# blah blah Success. blah blah
[postgres@tinman ~]$ createuser --interactive
Enter name of role to add: drone
Shall the new role be a superuser? (y/n) y
[postgres@tinman ~]$ createdb drone
[postgres@tinman ~]$ 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.