Each day I learn something new while doing software development. From today, I have started documenting them. I will be posting/sharing tips and tricks that I discover.
Learning 1: Creating a gzip dump of PostgreSQL database
One of the tasks that I had to do today was to take PostgreSQL dump in gzip format. To take PostgreSQL dump, we use pg_dump utility. We pipe its output to gzip utility. The gzip utility has -9 or —best flag for best possible conversion. The command below demonstrates how to do it.
$ pg_dump -U postgres dbname | gzip --best > dbexport.sql.gz
Learning 2: Using database init scripts with Docker Compose for PostgreSQL
In my previous project, I did with MySQL database so I knew this should be possible in PostgreSQL as well. The database dump that I was trying to initialise my database was big so I gzipped it. The full docker-compose.yaml is shown below for your reference.
version: '3'
services:
postgres-service:
image: postgres
restart: always
ports:
- "15432:5432"
environment:
- DEBUG=false
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=dbname
volumes:
- pgdata:/var/lib/postgresql
- ./dbinit/dbexport.sql.gz:/docker-entrypoint-initdb.d/dbexport.sql.gz
volumes:
pgdata:
Learning 3: Change owner of all tables in PostgreSQL
This is the third thing that I learnt today. You can change all the table owners by running following command.
$ for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done
To learn more you can refer this stackoverflow question.
Learning 4: How to find non-ascii hidden characters in IntelliJ
For some reason, the sql file that I had was having some non-ascii hidden characters. So, when I was running my sql as part of an automation script it was failing. To find these hidden non-ascii characters I use [^\x00-\x7F] regular expression. This made them visible and I was able to remove them.
I hope you enjoy these tips and find them useful.
Shekhar Gulati 