Step-by-Step Guide to Deploy PostgreSQL 12 and TimescaleDB on CentOS 7

Preface

Time Series Database (TSDB) is a specialized database for storing and managing time‑series data, characterized by high‑write, low‑read workloads, hot‑cold separation, high‑concurrency writes, no transaction requirements, and massive continuous ingestion. It is widely used in IoT, finance, monitoring, manufacturing, agriculture, and many other scenarios.

Our company needs a TSDB; after research we chose TimescaleDB, a powerful extension built on PostgreSQL.

Because the production environment runs PostgreSQL 12, the following steps show how to deploy PostgreSQL 12 on CentOS 7.5.

1. Environment Overview

OS: CentOS 7.5 (kernel 5.7), Server: 8 CPU × 32 GB RAM.

Download PostgreSQL source from

https://www.postgresql.org/download

.

Note: kernel version matters; for kernel 5.7 no hugepage configuration is needed, but several sysctl parameters must be set.

2. Kernel Parameter Modification

<code>vi /etc/sysctl.conf
kernel.shmmax = 30923764531
kernel.shmall = 7340032
fs.file-max = 1000000

sysctl --system

vi /etc/security/limits.conf
* soft nofile 1000000
* hard nofile 1000000
* soft noproc 1000000
* hard noproc 1000000

ulimit -n 1000000
</code>

3. Ensure gcc and make are installed

<code>gcc --version
make --version
</code>

4. PostgreSQL Service Deployment

<code>yum -y install readline readline-devel zlib zlib-devel bison

groupadd postgres
useradd -g postgres postgres
passwd postgres

# Create directories
mkdir -p /usr/local/postgresql-12
mkdir -p /data/postgresql-12/{data,log,archive}
chown -R postgres.postgres /data/postgresql-12/
chmod -R 0700 /data/postgresql-12/data

# Extract source
tar xf postgresql-12.6.tar.gz
cd postgresql-12.6

# Configure and compile
./configure --prefix=/usr/local/postgresql-12 --with-pgport=5432
make world
make install-world

# Add environment variables
vi /etc/profile
LANG=en_US.utf8
PGDATA=/data/postgresql-12/data
PGHOME=/usr/local/postgresql-12
LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
PATH=$PATH:/usr/local/postgresql-12/bin
export LANG PGDATA LD_LIBRARY_PATH PATH
</code>

5. PostgreSQL Initialization

<code>su - postgres
initdb -D /data/postgresql-12/data
</code>

Explanation: -D specifies the data directory.

6. Start and Stop PostgreSQL

<code># Start
pg_ctl -D /data/postgres-12/data -l postgresql.log start

# Stop
pg_ctl stop -D /data/postgres-12/data -mf
</code>

Explanation: -D specifies the data directory (optional). -l writes logs to the given file. -mf uses fast shutdown mode. -m selects shutdown mode: smart, fast, immediate. smart waits for all clients to disconnect (slow but safest). fast kills client connections quickly (recommended for production). immediate is like kill -9 and is unsafe.

7. Set PostgreSQL Password and Configure Remote Access

<code>su - postgres
ALTER USER postgres WITH PASSWORD 'your_password';

# Restart
pg_ctl restart -mf

# Allow remote connections
cd /data/postgresql-12/data
vi pg_hba.conf
# Add line, e.g.:
host    all             all             10.10.0.0/16            trust

vi postgresql.conf
# Change listen_addresses from 'localhost' to '0.0.0.0'

pg_ctl restart -D /data/postgres-12/data

# Test remote connection
psql -U postgres -h 10.10.22.151 -p 5432 -d postgres
</code>

8. PostgreSQL User and Role Management

PostgreSQL does not distinguish between users and roles; CREATE USER is an alias for CREATE ROLE with the LOGIN attribute.

<code># List roles
SELECT * FROM pg_roles;

# List users
SELECT * FROM pg_user;
</code>

8.1 Role Attributes

<code>login        – role can be used to connect
superuser    – database superuser
createdb     – permission to create databases
createrole   – permission to create or drop other roles
replication  – used for streaming replication
password     – password authentication (depends on client settings)
inherit      – role inherits privileges of member roles
</code>

8.2 Granting Roles

<code># Grant login privilege
ALTER ROLE allen WITH LOGIN;

# Grant create role privilege
ALTER ROLE jol WITH CREATEROLE;

# Create role with createdb and password, and allow login
CREATE ROLE allen CREATEDB PASSWORD 'allen123' LOGIN;

# Connect as new role
psql -U allen -d mydb -h 10.10.22.151 -p 5432;

# Grant all privileges on database
GRANT ALL PRIVILEGES ON DATABASE mydb TO allen;

# After switching to the database, grant privileges on all tables in public schema
\c mydb
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO allen;

# Example table creation and data insertion
CREATE TABLE student (
    name varchar(80),
    age int
);
INSERT INTO student VALUES ('allenjol', 22);
SELECT * FROM student;
</code>

This concludes the deployment and basic user management; next we will cover PostgreSQL master‑slave streaming replication.