postgresql

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:...

Like the title says, when restoring a Postgresql database onto a different server which happens to have a dfferent role owner, extra care is needed, otherwise similar error message below will come up: pg_restore: from TOC entry 217; 1259 17163 TABLE users_collections webdev1 pg_restore: error: could not execute query: ERROR: role "webdev1" does not exist Command was: ALTER TABLE public.users_collections OWNER TO webdev1; pg_restore: from TOC entry 218; 1259 17168 TABLE users_organizations webdev1 pg_restore: error: could not execute query: ERROR: role "webdev1" does not exist Command was: ALTER TABLE public....

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....

I need to install pg 0.17.1 gem on a server running CentOS. This is the error message I saw: # gem install pg -v '0.17.1' Building native extensions. This could take a while... ERROR: Error installing pg: ERROR: Failed to build gem native extension. /usr/bin/ruby extconf.rb checking for pg_config... no No pg_config... trying anyway. If building fails, please try again with --with-pg-config=/path/to/pg_config checking for libpq-fe.h... no Can't find the 'libpq-fe.h header *** extconf....

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....