tmaxopensql/postgres repository overview

⁠Tmax OpenSQL

Tmax OpenSQL is an open-source DBMS professional service platform brand of TmaxTibero, a DBMS specialized S/W company. TmaxTibero, Korea's leading DBMS company, provides PostgreSQL service as a one stop total service based on its own technology accumulated for 20 years.

⁠TmaxTibero's Technical Leadership

• Technical Support: 24*365 Call center, Direct technical support from vendor
• Consulting: More than 7,000 customized DB migration consulting experiences
• Patent: Technology power with 152 patents
• R&D Power: About 250 of R&D employees

Tmax OpenSQL provides the best integrated service for high availability, scalability, large-capacity processing, backup/recovery, security, encryption, compatibility, monitoring, and transition support technology based on TmaxTibero's unique technical experience and know-how.

⁠Get Started

• Learn about Tmax OpenSQL at TmaxTibero⁠
• Join Tmax OpenSQL's developer community⁠

⁠OpenSQL-PostgreSQL

⁠Supported tags and its Dockerfiles

• 14.0 : PostgreSQL 14.0 on redhat/ubi8

• 14.1 : PostgreSQL 14.1 on redhat/ubi8

• 14.2 : PostgreSQL 14.2 on redhat/ubi8

• 14.3 : PostgreSQL 14.3 on redhat/ubi8

• 14.4 : PostgreSQL 14.4 on redhat/ubi8

• 14.5 : PostgreSQL 14.5 on redhat/ubi8

• 14.6 : PostgreSQL 14.6 on redhat/ubi8

⁠How to use Tmax OpenSQL-PG image

docker run -v [absolute/path/on/host/OS]:[absolute/path/in/container] 
           --name [container_name] 
           hypersql/postgres:[tag_name] 

⁠Customize PostgreSQL instance

⁠Set variable from vars.yml

If you put it in the vars directory as vars.yml, the parameters are set when the container is created.

docker run -v /absolute/path/to/var/file:/tmp/settings/vars/vars.yml

or

docker run -v /absolute/path/to/vars/directory:/tmp/settings/vars

⁠Edit vars.yml

Parameters that can be applied to the autotuning, manage_dbserver, and init_dbserver roles are available. Please refer to the README.md of each role of pg-ansible for available parameters: https://github.com/hypersql/pg-ansible⁠

⁠Set superuser name (default: postgres)

pg_superuser: "super_username"

⁠Set password for superuser (required)

pg_superuser_password: "xxxxx"

⁠Set UID

pg_owner_id: 1000

⁠Set GID

pg_group_id: 1000

⁠Set Database Cluster Directory (default: /var/lib/pgsql/verion/data)

pg_data: "/pgdata"

⁠Set Log Directory (default: /var/log/postgres)

pg_log: "/pglog"

⁠Set WAL Directory (default: $PGDATA/pg_wal)

pg_wal: "/pgwal"

⁠Create Database

pg_databases:
  - name: "database_name"
    owner: "super_username"

⁠Set PostgreSQL parameter

pg_postgres_conf_params:
  - name: "archive_command"
    value: "/bin/true"

⁠Set max Connection(default: auto by cpus, min: 100)

pg_max_connections: 50

⁠Limit Resource docker container

When a Tmax OpenSQL-PG image is created, it automatically tunes its parameters by referring to docker container isolated resources. Parameters to be tuned are set through core and memory, and the value to be tuned can be changed through docker container resource isolation.

⁠Limited Memory

docker run --memory="300m"

⁠Limited CPU

• Set number of cpus

  Specifies that only CPU cores 0 to 2 are used.

  docker run --cpuset-cpus="0-2"
  

  Copy

• Set CPUs period and quota

  Set the container's CPU CFS quota and scheduler period. The quota defines how much to allocate in the cpu-period setting. The scheduler period does not change the default value of 100000 (100 ms) in most cases.

  docker run --cpu-period=100000 --cpu-quota=50000
  

  Copy

  It can set it as a percentage of CPU to easily set the CFS scheduler allocation.

  Example Using 50% CPU:

  docker run --cpus=".5"
  

  Copy

⁠Recommend docker volume mount in Tmax OpenSQL

When mounting a volume in docker, the permission of the container user and the HostOS user can be a problem. Volumes created by docker are stored in the /var/lib/docker/volumes/ path, making it difficult for users with other privileges to access them. Therefore, opensql recommends creating and using a docker volume rather than directly mounting the volume.

If you want to know the path of the created volume, use the following command.

docker volume inspect <volume_name>

• Create volume in docker

  docker create volume-1
  docker run -v volume-1:/path/want/to/mount
  

  Copy

• Create volume in docker-compose Declared volumes can be reused in other docker containers.

  • docker-compose.yml

  services:
    pg:
      image: hypersql/postgres:14.6
      ports:
        - "5432":"5432"
      volumes:
        - volume-1:/path/want/to/mount
  
  volumes:
    volume-1:
  

  Copy

⁠User Migration from Old Image

Migration is required to use what was used in the previously used hypersql docker image.

The migration image is set according to the new image, and the migration container needs the previously used environments.

• Example

docker run -v /absolute/path/to/external/volume:/hypersql/pgdata/ \
           -v /absolute/path/to/external/volume:/hypersql/pgwal/ \
           -e POSTGRES_USER={PG_USER} \
           -e POSTGRES_PASSWORD=${PG_PASSWORD} \
           -e POSTGRES_DB=${DB_NAME}} \
           -e POSTGRES_DATADIR=${DATADIR} \
             hypersql/postgres:migration-14

After the migration is complete, the new image runs normally.

⁠PostgreSQL Autotune

PostgreSQL is autotuned based on:

max_connections

• vcpus*4 (at least 100) or pg_max_connections

shared_buffers

• shared_buffers will not exceed 64GB.
• (Memory < 3GB) shared_buffer = Memory / 8
• (3GB ≤ Memory < 8GB) shared_buffer = Memory / 4 * 0.75
• (8GB ≤ Memory < 64GB) shared_buffer = Memory / 4
• (64GB ≤ Memory) shared_buffer = Memory / 24 (at least 16GB)

work_mem

• (Total Memory - shared_buffers) / (pg_max_connections*16)

maintenance_work_mem

• It does not exceed 1024 MB.
• (Total Memory - shared_buffers) * 15 / 100 / (autovacuum_max_worker)

effective_cache_size

• shared_buffers + { (sys_buffers_kb + sys_cached_kb + sys_slab_reclaimable_kb) /1024}

wal_compression

• "on"

wal_log_hints

• "on"

wal_buffers

• 64MB

checkpoint_timeout

• 5min

checkpoint_completion_target

• 0.9

cpu_tuple_cost

• 0.03

autovacuum_max_workers

• 5

autovacuum_vacuum_cost_limit

• 3000

idle_in_transaction_session_timeout

• 10min

default_statistics_target

• 100

hash_mem_multiplier

• 2.0