-

   rss_rss_hh_new

 - e-mail

 

 -

 LiveInternet.ru:
: 17.03.2011
:
:
: 51

:


1 Fresh. . .

, 17 2017 . 15:44 +
1C Fresh. . PostgreSQL , .

image




1 MS SQL Server. Fresh , , Microsoft. PostgreSQL, 1 1 Fresh. , () 3.0, 600 . Linux . . Windows () Linux. , Linux , .

, PostgreSQL. , , .

deb- PostgreSQL 8.4.3-3.1C, 1, . , Debian Wheezy, oldstable , apache2.2 ( apache2.4 1 ). , oldstable. PostgreSQL libc6 Debian Jessie. , , NFS- . Linux . ( ), COM-, , . PostgreSQL, 1 .

PostgreSQL 9.6 Postgres Professional. , , . 1 PostgreSQL, 9.6.3, . 1 .

Debian Jessie .

Zabbix, :



, PostgreSQL 9.6.

:

image

, :

image

, , . 2 , 8 40 . SSD. 7 3.0 (200-800 ). .

PostgreSQL

, . , , WAL , .

PostgreSQL, , PostgreSQL , , WAL, (PiTR).

:

, Debian 8 Jessie, PostgreSQL 9.6 Postgres Professional.

:

# pg_createcluster 9.6 -p 5433 -d /databases/db_01


/databases db_01, , /etc/postgresql/9.6/db_01/. 9.6 PostgreSQL 5433. , :

# pg_lsclusters


postgresql.conf, PgTune. , , PgTune .

max_parallel_workers_per_gather , . . , , , .

max_locks_per_transaction - 64, 300. , . -, -.

, pg_xlog .

:

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

data_directory = '/db/disk_database_db_01/db_01' 
hba_file = '/etc/postgresql/9.6/db_01/pg_hba.conf'
ident_file = '/etc/postgresql/9.6/db_01/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.6-db_01.pid'

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*' 
port = 5433
max_connections = 100 
unix_socket_directories = '/var/run/postgresql' 
ssl = true 
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' 
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

shared_buffers = 1536MB
work_mem = 7864kB
maintenance_work_mem = 384MB 
dynamic_shared_memory_type = posix
shared_preload_libraries = 'online_analyze, plantuner,pg_stat_statements' 


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

wal_level = replica 
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

max_wal_senders = 2
wal_keep_segments = 32

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
effective_cache_size = 4608MB

#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------

stats_temp_directory = '/var/run/postgresql/9.6-db_01.pg_stat_tmp'

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

datestyle = 'iso, dmy'
timezone = 'localtime'
lc_messages = 'ru_RU.UTF-8' # locale for system error message
 # strings
lc_monetary = 'ru_RU.UTF-8' # locale for monetary formatting
lc_numeric = 'ru_RU.UTF-8' # locale for number formatting
lc_time = 'ru_RU.UTF-8' # locale for time formatting

default_text_search_config = 'pg_catalog.russian'

#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------

max_locks_per_transaction = 300 # min 10

#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
escape_string_warning = off
standard_conforming_strings = off


#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

online_analyze.threshold = 50
online_analyze.scale_factor = 0.1
online_analyze.enable = on
online_analyze.verbose = off
online_analyze.min_interval = 10000
online_analyze.table_type = 'temporary'
plantuner.fix_empty_table = false


:

# pg_ctlcluster 9.6 db_01 start


1 :

hostname port=5433

. VACUUM ANALYZE .



standby- .

master-
postgresql.conf, , /etc/postgresql/9.6/db_01/

listen_addresses = '*'
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 128


replica:

postgres=# CREATE ROLE replica WITH REPLICATION PASSWORD 'MyBestPassword' LOGIN;


slave-, pg_hba.conf

# TYPE	DATABASE	USER		ADDRESS		METHOD
host    replication	replica		192.168.0.0/24	md5


:

# pg_ctlcluster 9.6 db_01 restart


slave-. , , , , . :

# pg_ctlcluster 9.6 db_01 stop


postgresql.conf standby:
hot_standby = on


/databases/db_01/ :

# cd /databases/db_01
# rm -Rf /databases/db_01
# su postgres -c "pg_basebackup -h master.domain.local -p 5433 -U replica -D /databases/db_01 -R -P --xlog-method=stream"


recovery.conf, :

standby_mode = 'on'
primary_conninfo = 'user=replica password=MyBestPassword host=master.domain.local port=5433 sslmode=prefer sslcompression=1 krbsrvname=postgres'


failover, . recovery.conf .

:

# pg_ctlcluster 9.6 db_01 start


, . wal sender, wal receiver. :

SELECT *,pg_xlog_location_diff(s.sent_location,s.replay_location) byte_lag FROM pg_stat_replication s;


, :

SELECT now()-pg_last_xact_replay_timestamp();


WAL

pg_dump , 1C Fresh . , . WAL-.

WAL :
wal_level replica
archive_mode = on
archive_command , :

archive_command = 'test ! -f /wal_backup/db_01/%f && cp %p /wal_backup/db_01/%f'

WAL /wal_backup/db_01/

, . , WAL , , , WAL. WAL-.

#!/bin/bash
db="db_01"
wal_arch="/wal_backup"
datenow=`date '+%Y-%m-%d %H:%M:%S'`
mkdir /tmp/pg_backup_$db
su postgres -c "/usr/bin/pg_basebackup --port=5433 -D /tmp/pg_backup_$db -Ft -z -Xf -R -P"
test -e ${wal_arch}/$db/base.${datenow}.tar.gz && rm ${wal_arch}/$db/base.${datenow}.tar.gz
cp /tmp/pg_backup_$db/base.tar.gz ${wal_arch}/$db/base.${datenow}.tar.gz


(PiTR)

# pg_ctlcluster 9.6 db_01 stop
# rm -Rf /databases/db_01


, ( ) recovery.conf :

restore_command = 'cp /wal_backup/db_01/%f %p'
recovery_target_time = '2017-06-12 21:33:00 MSK'
recovery_target_inclusive = true


2017-06-12 21:33:00 MSK, .



1 Fresh PostgreSQL , , . , , , , .

1C Fresh PostgreSQL:

image

350 , . !
Original source: habrahabr.ru (comments, light).

https://habrahabr.ru/post/333480/

:  

: [1] []
 

:
: 

: ( )

:

  URL