Initialize the data directory

Before initializing, you need to determine the data storage directory of AntDB, which in this document is assumed to be: /data/antdb/data. The initialization is done via the initdb command as follows.

initdb -D /data/uat/data --wal-segsize=1024

The data directory is not required to exist, if it already exists, make sure the directory is empty. The antdb operating system user needs to have read and write access to the data directory. After the initialization is complete, a series of files and directories will be created in the /data/antdb/data directory.

Modify database parameters

In the data directory, the file with the file name postgresql.conf is the configuration file for the database parameters, and the user can modify this file to change the database parameters. The configuration can be appended at the end of the file.

cat >> /data/uat/data/postgresql.conf <<EOF
listen_addresses='*'
superuser_reserved_connections=13
tcp_keepalives_idle=60
tcp_keepalives_interval=5
tcp_keepalives_count=10
work_mem=16MB
min_wal_size=2GB
maintenance_work_mem=1GB
max_stack_depth=7000
wal_sync_method=open_datasync
wal_compression=on
wal_log_hints=on
checkpoint_timeout=15min
checkpoint_completion_target=0.9
archive_mode=on
archive_command='/bin/date'
wal_keep_segments=64
log_destination='csvlog'
logging_collector=on
log_directory='pg_log'
log_rotation_size=100MB
log_min_messages=error
log_statement=ddl
track_activity_query_size=2048
autovacuum_max_workers=5
autovacuum_vacuum_threshold=500
autovacuum_analyze_threshold=500
max_locks_per_transaction=256

The following parameters, which need to be determined according to the actual requirements, are replaced by the bolded and italicized parts.

• port=custom database port number, recommended to be set to 15432 if not required.

• max_connections=custom maximum number of connections.

• max_prepared_transactions=equal to the maximum number of connections.

• max_worker_processes=cpu * 2.

• shared_buffers=physical_memory * 25%GB.

• effective_cache_size=physical_memory * 75%GB.

• max_wal_size=2 * shared_buffersGB.

• random_page_cost= set to 1 if SSD disks; keep the default value of 4 if SATA disks.

Configure AntDB whitelist

There is a file named pg_hba.conf in the data directory of AntDB, this file is the file to configure the access whitelist, the configuration format is as follows.

# TYPE DATABASE USER ADDRESS METHOD

ADDRESS consists of IP network segment/subnet mask, please configure it by yourself according to the actual situation. For example, add a configuration in hba that allows IP segments connected to the database to access all databases through md5 authentication.

cat >> /data/uat/data/pg_hba.conf << EOF
host    all             all          10.0.0.0/8            md5
EOF

Note: IPV4 or IPV6 addresses can be specified in host, but the specification needs to be unified. That is: either specify the IPV4 address uniformly, or the IPV6 address uniformly. The details can be found at: https://www.postgresql.org/docs/13/auth-pg-hba-conf.html

Start the database

After initialization, you can start the database with the pg_ctl command, -D refers to the data directory specified during initialization.

pg_ctl start -D /data/antdb/data

After the initialization is completed, you can use the psql command to log in to the database.

psql -d postgres -p 5432

At this point, the AntDB database installation and startup is complete.

If you need to build a standby machine, please refer to the chapter of building a single cluster with one master and two standby machines.