暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Postgresql多租户实践--使用多个Instance篇

Postgresql多租户实践--使用多个Instance篇

长河的笔记 2020-07-19
1488

目标：为每个租户或应用提供一个database cluster(实例)，即在同一台服务器上运行多个数据库实例，多个实例的监听端口不同。


本文实验环境:PostgreSQL11.8


步骤:

创建2个目录pgdata1和pgdata2，用于存放每个database cluster(实例)的数据。

-bash-4.2$ mkdir -p /u01/{pgdata1,pgdata2}


使用initdb工具创建2个database cluster 实例, 分别对应上面新创建的2个目录。命令是以postgres用户执行的，所以实例的进程和数据文件的属主都是postgres用户。默认字符集是UTF8， 文本分词语言等配置是英文， 最大连接100，共享缓冲区128MB，时区等等。最后提示启动实例的命令。

-bash-4.2$ initdb -D /u01/pgdata1
-bash-4.2$ initdb -D /u01/pgdata2
--其中的一个命令输出如下：
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.


The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".


Data page checksums are disabled.


fixing permissions on existing directory /u01/pgdata1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.


Success. You can now start the database server using:


    pg_ctl -D /u01/pgdata1 -l logfile start



测试:

如何区分不同实例的进程和为每个实例指定不同的端口?

初始化实例之后，需要修改下默认的监听端口和实例标识，例如：

vi /u01/pgdata1/postgresql.conf

port = 5432

cluster_name = 'pgdata1'


vi /u01/pgdata2/postgresql.conf

port = 5433

cluster_name = 'pgdata2'


启动实例，可以看到pgdata1监听端口是5432，pgdata2监听端口是5433。而且每个实例的进程名称中均包括设置的cluster_name。

-bash-4.2$ pg_ctl start -D /u01/pgdata1
waiting for server to start....2020-07-16 16:43:21.339 CST [126087] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-07-16 16:43:21.342 CST [126087] LOG:  could not create IPv6 socket for address "::1": Address family not supported by protocol
2020-07-16 16:43:21.343 CST [126087] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-07-16 16:43:21.345 CST [126087] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-07-16 16:43:21.371 CST [126087] LOG:  redirecting log output to logging collector process
2020-07-16 16:43:21.371 CST [126087] HINT:  Future log output will appear in directory "log".
 done
server started
-bash-4.2$ pg_ctl start -D /u01/pgdata2
waiting for server to start....2020-07-16 16:43:25.873 CST [126108] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2020-07-16 16:43:25.876 CST [126108] LOG:  could not create IPv6 socket for address "::1": Address family not supported by protocol
2020-07-16 16:43:25.877 CST [126108] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2020-07-16 16:43:25.878 CST [126108] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2020-07-16 16:43:25.909 CST [126108] LOG:  redirecting log output to logging collector process
2020-07-16 16:43:25.909 CST [126108] HINT:  Future log output will appear in directory "log".
 done
server started


-bash-4.2$ ps -ef | grep post
postgres 126087      1  0 16:43 pts/0    00:00:00 /usr/pgsql-11/bin/postgres -D /u01/pgdata1
postgres 126093 126087  0 16:43 ?        00:00:00 postgres: PGDATA1: logger
postgres 126097 126087  0 16:43 ?        00:00:00 postgres: PGDATA1: checkpointer
postgres 126098 126087  0 16:43 ?        00:00:00 postgres: PGDATA1: background writer
postgres 126099 126087  0 16:43 ?        00:00:00 postgres: PGDATA1: walwriter
postgres 126100 126087  0 16:43 ?        00:00:00 postgres: PGDATA1: autovacuum launcher
postgres 126101 126087  0 16:43 ?        00:00:00 postgres: PGDATA1: stats collector
postgres 126102 126087  0 16:43 ?        00:00:00 postgres: PGDATA1: logical replication launcher
postgres 126108      1  0 16:43 pts/0    00:00:00 /usr/pgsql-11/bin/postgres -D /u01/pgdata2
postgres 126113 126108  0 16:43 ?        00:00:00 postgres: PGDATA2: logger
postgres 126117 126108  0 16:43 ?        00:00:00 postgres: PGDATA2: checkpointer
postgres 126118 126108  0 16:43 ?        00:00:00 postgres: PGDATA2: background writer
postgres 126119 126108  0 16:43 ?        00:00:00 postgres: PGDATA2: walwriter
postgres 126120 126108  0 16:43 ?        00:00:00 postgres: PGDATA2: autovacuum launcher
postgres 126121 126108  0 16:43 ?        00:00:00 postgres: PGDATA2: stats collector
postgres 126122 126108  0 16:43 ?        00:00:00 postgres: PGDATA2: logical replication launcher


--停止实例
-bash-4.2$ pg_ctl stop -D /u01/pgdata2
waiting for server to shut down.... done
server stopped
-bash-4.2$ pg_ctl stop -D /u01/pgdata1
waiting for server to shut down.... done
server stopped



相关文章：

Postgresql多租户实践--使用多个Schema篇

Postgresql多租户实践--使用多个Database篇

Postgresql多租户实践--使用多个Instance篇


postgresql
文章转载自长河的笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论