The Postgres conversion function is often used to instantly help PMs understand the actual status of current users and to record common commands underneath. First install the Postgres environment, this side is actually the way to use Docker to start a new Postgres DB.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

  db:
    image: postgres:12
    restart: always
    volumes:
      - pg-data:/var/lib/postgresql/data
    logging:
      options:
        max-size: "100k"
        max-file: "3"
    environment:
      POSTGRES_USER: db
      POSTGRES_DB: db
      POSTGRES_PASSWORD: db

The environment parameter above can be freely adjusted, and then the database can be started for app streaming via docker-compose up -d.

Login to Postgres

Assuming the Docker container does not have an expos 5432 port, you basically have to log in to the container. But if expose out, then the Host machine needs to install the postgres client package to be able to log in, and we can log in to the container directly to avoid the problem that the clinet tool and server version are not the same.

1

docker-compose exec db /bin/bash

where db is the name in docker-compose.yml, please change it yourself. After entering the container, you can login to the postgres database by using the psql command.

1

psql -h 127.0.0.1 -d db -U db -W

Exporting data to CSV format

After logging into Postgres, a single table can be exported to csv format with a simple command

1

some_table TO '/tmp/data.csv' DELIMITER ',' CSV HEADER;

If you just want a few columns in the table, you can change it to the following

1

public.user(id, email) TO '/tmp/data.csv' DELIMITER ',' CSV HEADER;

Or to organize the data through SQL

1

(select id, email from public.user) TO '/data.csv' DELIMITER ',' CSV HEADER;

You can also convert to Taiwan time for UTC time

1
2
3
4

select state, email, simulator, a.created_at \
  at time zone 'utc' at time zone 'Asia/Taipei' as created_at \
  from public.simulation a join public.user b on a.user_id = b.id  \
  order by a.created_at desc limit 5

Take out the data

The above data will be stored in /data.csv in the container, so you can pull out the data by docker cp command. Find the postgres container ID with docker ps and run the following command

1

docker cp container_id:/tmp/data.csv .

Then you can automate the process for all the above steps.