TIL #1 | PostgreSQL Gzip Dump, Docker-Compose, Change owner of PostgreSQL tables, and finding non-ascii characters in IntelliJ


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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s