postgres

Recently, I upgraded a server running Ubuntu 22.04 to 24.04, that also comes with PostgreSQL 16. For some odd reasons, the Postgres server was not managed by Ansible, and changes previously made to Postgres 15 was not recorded. I noticed that the backup database (pg_dump) was not working properly, the backup database size was just a few kilobytes. Let’s investigate, shall we? root@toto:/etc/cron.daily# ./postgresql-backup pg_dump: error: connection to server on socket "/var/run/postgresql/....

I recently upgraded the PostgreSQL server on one my server from version 14 to 16. Why the big jump? This was due to the OS running on this server was upgraded from Ubuntu 22.04 to 24.04. When the database was restored from the database dump, the application that requires that the database detects the new version and tried to perform the database migration. That’s when it failed and produced the following error message:...

Normally to drop a database in PostgreSQL, we can just type: DROP DATABASE database; Here’s a list of my current databases: postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+-----------+----------+-------------+-------------+----------------------- dashboard | dashboard | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres-db | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | puppetdb | puppetdb | UTF8 | en_US....

Before I wrote this blog post, I had thought there was no binary package for PostgreSQL server for FreeBSD 11 running on Raspberry Pi. Hmm… how wrong I was! I just wasted the whole night compiling Postgres from source. DOH! Anyhow, here’s the step I took to do it. If you’re familiar with FreeBSD, there’s nothing new here. But, I only use FreeBSD once in awhile, and so I tend to forget a lot what I do....

Ever since Oracle took the ownership of MySQL, I’ve been leaning more and more toward PostgreSQL, or other NoSQL database like MongoDB. Also, I’m not completely new to PostgreSQL, but the fact that I only have to touch it once in awhile, makes me feel PostgreSQL is so hard to use and missing the goold-oldie MySQL. Anyway, this is the issue I had: # su - postgres -bash-4.1$ psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US....