Restoring Postgresql DB backup with different owner role

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

July 3, 2022 · 1 min · 213 words · kenno

PostgreSQL: Drop a database which has a hyphen in name

Normally to drop a database in PostgreSQL, we can just type: DROP DATABASE database; Here’s a list of my current databases: And I want to drop a database called “postgres-db”. So, let’s try: According to this, we have to escape the database name which contains a dash or hyphen in double quotation marks: It works.

January 22, 2016 · 1 min · 55 words · kenno

Install PostgreSQL server on Raspberry Pi from FreeBSD port

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

June 18, 2015 · 2 min · 426 words · kenno

Can't find the 'libpq-fe.h header

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

February 3, 2014 · 1 min · 192 words · kenno

PostgreSQL: Peer authentication failed for user

Ever since Oracle took the ownership of MySQL, I’ve been moving toward PostgreSQL, or other NoSQL database like MongoDB. 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: To fix it, we need to modify: pg_hba.conf. This file is located in /var/lib/pgsql/9....

January 23, 2014 · 1 min · 89 words · kenno