.pgpass用于Dockerized环境中的PostgreSQL复制 [英] .pgpass for PostgreSQL replication in Dockerized environment

问题描述

我尝试使用Docker和bash脚本（我使用Coreos）设置PostgreSQL从站。我还找不到提供有效的 .pgpass 的任何方法。

I try to set up an PostgreSQL slave using Docker and a bash script (I use Coreos). I have not found any way to supply a valid .pgpass.

我知道我可以创建PGPASSWORD环境变量，但出于安全原因不希望这样做（如此处所述， http://www.postgresql.org/docs/current/static/libpq-envars.html ），并且因为每次使用recovery.conf文件（对于primary_conninfo变量）都应该可以访问此密码。

I know I could create a PGPASSWORD environment variable, but do not wish to do so for security reasons (as stated here, http://www.postgresql.org/docs/current/static/libpq-envars.html),, and because this password should be accessible every time the recovery.conf file is used (for the primary_conninfo variable).

Dockerfile

Dockerfile

# ...
# apt-get installs and other config
# ...

USER postgres
# Create role and db
RUN /etc/init.d/postgresql start &&\
    psql --command "CREATE USER replicator WITH ENCRYPTED PASSWORD 'THEPASSWORD';" &&\
    psql --command "CREATE DATABASE db WITH OWNER replicator;"

# Set the pg_pass to allow connection to master
ADD ./pgpass.conf /home/postgres/.pgpass # pgpass.conf comes my root git folder
USER root
RUN chmod 0600 /home/postgres/.pgpass

在我的bash文件中

# ...

pg_basebackup -h host.of.master.ip -D /var/pgbackup/backup_data -U replicator -v -P

# ...

问题似乎是pgpass文件未读取。似乎我应该使用我要模仿的用户密码（ https：// serverfault.com/questions/526170/psql-fe-sendauth-no-password-supplied ），但在这种情况下，复制器角色自然不是可用的bash用户。 （请注意，既不能将pgpass复制到/ home / root，也不能将/ home / postgres复制）。

The problems seems to be that the pgpass file is not read. It seems I should use the password of the user I'm sudoing to (https://serverfault.com/questions/526170/psql-fe-sendauth-no-password-supplied), but in this case the replicator role is naturally not an available bash user. (Note that neither copying the pgpass to /home/root not /home/postgres works).

注意：我的pgpass文件和远程数据库conf

Note: my pgpass file and by remote database conf

# pgpass.conf
host.of.master.ip:5432:replication:replicator:THEPASSWORD
host.of.master.ip:5432:*:replicator:THEPASSWORD

# pg_hba.conf
host    replication   replicator    host.of.slave.ip/24    md5

推荐答案

您必须在上创建 .pgpass 将要运行命令的用户的主文件夹（在本例中为 postgres ）。文件的每一行都必须采用格式 主机名：端口：数据库：用户名：密码并支持通配符，因此您只需将数据库设置为 *即可。

You have to create a .pgpass on the home folder of the user who's going to be running the commands (in this case, postgres). Each line of the file has to be in the format hostname:port:database:username:password and supports wildcards, so you can just set the database to "*" for example.

就我而言，我有这样的事情……

In my case, I have something like this...

$ sudo echo "${PRIMARY_IP}:5432:*:${REPL_USER}:${REPL_PASS}" > /var/lib/postgresql/.pgpass
$ sudo chown postgres:postgres /var/lib/postgresql/.pgpass
$ sudo chmod 0600 /var/lib/postgresql/.pgpass
$ sudo -u postgres pg_basebackup -h $PRIMARY_IP -D /var/lib/postgresql/9.4/main -U ${REPL_USER} -v -P --xlog-method=stream

当我使用 -e PRIMARY_IP = xxxx 运行docker容器时，设置了这些变量（例如PRIMARY_IP）

Those variables (e.g. PRIMARY_IP) are set when I run the docker container with -e PRIMARY_IP=x.x.x.x

这篇关于.pgpass用于Dockerized环境中的PostgreSQL复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！