前提条件 :
1. recovery.conf里面配置了 trigger_file, 并且这个文件所载目录可以被启动postgresql数据库的OS用户写入。
2. 有数据库超级用户权限。
3. 数据库是hot-standby模式的, 也就是recovery.conf.standby_mode = on, postgresql.conf.hot_standby = on .
举例 :
recovery.conf 配置了 :
trigger_file = '/pgdata/data01/promote.pg.5432'
连接到hot-standby超级用户,
psql -h 127.0.0.1 standby postgres
# COPY (SELECT now()) TO '/pgdata/data01/promote.pg.5432';
激活。
这种方法适用于远程执行, 因为COPY是服务端接口, 生成的文件也是写入到服务端的, 不会写到客户端.
例如
postgres@db6-> psql -h 172.16.3.150 -p 1921 -U postgres postgres
Password for user postgres:
psql (9.1.2, server 9.4.1)
WARNING: psql version 9.1, server version 9.4.
Some psql features might not work.
Type "help" for help.
以下COPY, 将文件写入数据库服务器的/home/postgres/abc, 而不是本地的/home/postgres/abc
postgres=# copy (select 1) to '/home/postgres/abc';
COPY 1
[postgres@db6 ~]$ ls -la /home/postgres/abc
ls: /home/postgres/abc: No such file or directory
pg_read_file也是服务端函数, 是查看服务端的$PGDATA/postgresql.conf
postgres=# select * from pg_read_file('postgresql.conf');
pg_read_file
---------------------------------------------------------------------------------
listen_addresses = '0.0.0.0' # what IP address(es) to listen on; +
port = 1921 # (change requires restart) +
max_connections = 1000 # (change requires restart) +
superuser_reserved_connections = 13 # (change requires restart) +
unix_socket_directories = '.' # comma-separated list of directories +
shared_buffers = 4096MB # min 128kB +
maintenance_work_mem = 64MB # min 1MB +
dynamic_shared_memory_type = posix # the default is the first option +
synchronous_commit = off # synchronization level; +
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each+
log_destination = 'csvlog' # Valid values are combinations of +
logging_collector = on # Enable capturing of stderr and csvlog +
log_truncate_on_rotation = on # If on, an existing log file with the +
log_min_duration_statement = 1s # -1 is disabled, 0 logs all statements +
log_checkpoints = on +
log_connections = on +
log_disconnections = on +
log_error_verbosity = verbose # terse, default, or verbose messages +
log_lock_waits = on # log lock waits >= deadlock_timeout +
log_timezone = 'PRC' +
datestyle = 'iso, mdy' +
timezone = 'PRC' +
lc_messages = 'C' # locale for system error message +
lc_monetary = 'C' # locale for monetary formatting +
lc_numeric = 'C' # locale for number formatting +
lc_time = 'C' # locale for time formatting +
default_text_search_config = 'pg_catalog.english' +
deadlock_timeout = 10ms +
(1 row)
