postgresql[9.4-9.6]--Server Management

first, compile and install:

• Environment Preparation:
  • GNU make version >=3.8 (make--version)
  • Iso/ansi C compiler, at least compatible with C89 standard, GCC or Intel translator, etc. can be
  • gzip, bzip2, tar, source unpacking
  • GNU Readline Library for psql operation history commands
  • Security related modules: Kerberos, OpenSSL, OpenLDAP, PAM, etc., need to install the corresponding package in advance.
  • If you want to use process language such as Pl/python, Pl/perl, PL/TCL, and so on, the language itself and the associated development Library are installed
  • If you are using the source code in Git or need to do server-side development, you need GNU Flex (>=2.5.31) and Bison (>=1.875), and you cannot use other Lex or YACC programs
  • Disk space: Editing process source tree occupies 100m+, generated program file 20m+, an empty database data directory 30m+, regression test space 150m+; Note: A database occupies approximately five times times the space occupied by flat files that store equivalent data.

• Configure editing Options:
  • --prefix=prefix Specify the installation directory, default to/usr/local/pgsql
  • --bindir= executable directory, default/prefix/bin
  • --sysconfdir= configuration file directory, default/prefix/etc
  • --libdir= library file directory, default/prefix/lib
  • --includedir= header file directory, default/prefix/include
  • --datarootdir=datarootdir set read-only shared files directory, default to/prefix/share
  • --mandir= Man manual catalog, default/datarootdir/man
  • --with-extra-version=string appends a string string to the version number for use as a custom version ID
  • --WITH-PGPORT=NUMBER Specifies the default port number for the server side and client, which defaults to 5432
  • --WITH-OPENSSL for SSL support, prior to installing the OpenSSL package
  • --with-pam Edit Pam Support
  • --with-ldap the LDAP support, need to install OpenLDAP package beforehand
  • --with-segsize= set segment size, in gigabytes (GB), default to 1GB, large tables are divided into multiple files by segsize
  • --with-blocksize= Specifies the block size, which is the basic unit of storage and I/O in the table, the default 8Kbytes, usually without change, with a range of values 1-32kb
  • --with-wal-segsize= set the segment size of the Wal (Write-ahead Logging), in megabytes (MB), default 16MB, value range 1-64MB
  • --with-wal-blocksize= Specifies the block size of the Wal, which is the basic unit of the Wal-write log storage and I/O, in Kbytes, default 8KB, value range 1-64KB
  • --enable-debug (compiles all programs and libraries with debugging symbols. This means, which can run the programs in a debugger to analyze problems), is used in the production environment when using the GCC editor, and other editors can affect performance; more for development
  • --enable-profiling The GCC editor, all programs and libraries will be translated for performance analysis, and the gmon.out generated at the backend exit is used for performance analysis; more for development
  • --enable-coverage code Coverage test, only for development
  • --enable-cassert Assertion (assertion) check on server, only for development
  • --enable-depend (enables automatic dependency tracking. With this option, the makefiles was set up so, and all affected object files would be rebuilt if any header file is Chang ED), only for development
  • --ENABLE-DTRACE Dynamic Tracking Tool DTrace support, currently not available for Linux platforms, for FreeBSD, Solaris
  • --WITH-SYSTEMD Open SYSTEMD Support, 9.6 and later

• Configure environment variables, you can specify environment variables on the command line, such as:./configure cc=/opt/bin/gcc cflags= '-o2-pipe '
  • CC designation C Translator
  • CFLAGS Setting C Editor Options
  • CPP Specifies the C-preset compiler
  • Cppflags Setting the C preset compiler option

• make options:
  • make by default configuration
  • make World" simultaneous document and add-on module (contrib)
  • make check regression test to test the integrity of the function after installation is complete and cannot be Root run
  • make Install installer
  • make install-docs Installation Documentation (info, man manual)
  • make install-world Install all installable
  • make uninstall delete installed files (The resulting directory cannot be deleted)
  • make clean clears the files generated by make, but retains configure generated files
  • make Distclean will source Revert to the original state, that is, delete both the make and configure phase generated files
  • make-c Specify the source file directory, only the specific parts of the installation program , such as installing only client applications and interfaces: Make-c Src/bin install; Make-c src/include Install; Make-c src/interfaces Install; Make-c doc install

Second, post-installation settings

• Shared libraries
  • Method One: Write in/etc/profile or ~/.bash_profile: Export ld_library_path=/usr/local/pgsql/lib, the target must be directory with the same as specified at the time of installation
  • Method Two: Create pgsql.conf in/etc/ld.so.conf.d/directory or directly manipulate/etc/ld.so.conf file, write/usr/local/pgsql/lib, then run Ldconfig update cache

• Environment variables
  • Write in/etc/profile or ~/.bash_profile:
    • Export Path=/usr/local/pgsql/bin: $PATH
    • Export Manpath=/usr/local/pgsql/man: $MANPATH
  • The variable PGHOST and PGPORT are used to specify the host and port of the database server for the client application, which overrides the default when editing

Third, server Setup and operation

• PostgreSQL user Account
  • Proposed principle: Create a separate private account that is dedicated to running the PostgreSQL server side as the owner of the database data directory and its parent directory, but cannot install the executable as belonging to this user
  • Useradd Add a system user, CreateUser create the corresponding PostgreSQL user with the same name, Createdb create the database
• Creating a Database Cluster (catalog Cluster)
  • Initdb-d/usr/local/pgsql/data
  • or set PGDATA environment variable to execute INITDB or Pg_ctl directly initdb
  • or pg_ctl-d/usr/local/pgsql/data initdb
• Use a separate data partition
  • Do not use the partition's item-level directory (the mount point) as the Database Cluster directory, you should establish a directory (parent directory), and then create a data directory in it, in order to avoid the problem of permissions, while preventing the data disturbance caused by the disconnection of the mount point
• Starting the Database Server
  • Pg_ctl start-l logfile-d/path/to/data or postgres-d/path/to/data >serverlog 2>&1 &, Serverlog have to be replaced with actual log text Piece path
  • When setting up PostgreSQL to boot, you cannot start with root or another user and should be used in startup scripts similar to: Su postgres-c ' pg_ctl start-l serverlog-d/path/to/data '
  • When the server is running, the PID is saved in the first line of the Postmaster.pid file in the data directory
• Service Startup FAQs
  • Port is occupied (there are already running PostgreSQL processes or other programs)
  • An attempt was made to use a reserved port (<1024), such as: Postgres-p 666
  • The shared memory (System-v-style) or semaphore (System V semaphores) is insufficient, and can often be due to kernel limitations or no corresponding support entry at all (If you get a "illegal system C All "Error,it was likely that shared memory or semaphores be supported in your kernel at all)
• Client Connectivity FAQs
  • The server does not start or is not configured to allow TCP/IP connections, and will typically receive ' Psql:could not connect to server: ... ' Similar bug hints
  • Client's own network problems, etc.
• Shared Memory (SHM) and semaphores (SEM)
  • Modify Kernel.shmmax in/etc/sysctl.conf and so on, immediate effect is required to execute sysctl-p
  • or change/proc/sys/kernel/shmall, etc.
• System Resource Limitations (/PROC/SYS/FS/FILE-MAX, etc.)
  • The number of processes per user
  • The number of open files per process
  • The amount of memory available to each process
• Coping with Out-of-memory (OOM) killer
  • Pid= ' head-n 1/path/to/postmaster.pid ' && echo-1000 >/proc/$Pid/oom_score_adj preferred
  • or pid= ' pidof postgres ' && echo-17 >/proc/$Pid/oom_adj is typically used for kernels older than 2.6
  • Kernel source see: ... src/linux/include/uapi/linux/oom.h
• Using Linux page memory
  • Change the value of/proc/sys/vm/nr_hugepages
  • or set/etc/sysctl.conf in vm.nr_hugepages=
• Shutting down the server
  • Typically using SIGTERM (waiting for all processes to exit gracefully, including backups) or SIGINT (similar to SIGTERM but not waiting for the backup process) signals, Sigquit is not recommended (processes with no normal exit within 5 seconds will be closed directly) and SIGKILL (Immediate and unconditional exit, this signal cannot close the child process simultaneously, and the shared memory and semaphore can be released too late)
  • such as: Kill-int ' head-1/path/to/postmaster.pid '
  • Note: You can send a signal to a child process as specified
• Upgrading a PostgreSQL Cluster
  • For the upgrade between minor version numbers, update the executable file directly and restart the server
  • the traditional method for moving data to a new major version was to dump and reload the database, though this can be slow
    1. pg_dumpall > OutputFile
    2. PG _ctl stop
    3. mv/usr/local/pgsql/usr/local/pgsql.old back up old version executables
    4. install the new version of PostgreSQL
    5. create A new database cluster if needed:/usr/local/pgsql/bin/initdb-d/usr/local/pgsql/data
    6. Configure the new server pg_hba.conf and postgresql.conf
    7. start The new database Server:pg_ctl start-d/usr/local/pgsql/data
    8. finally, Restore your data from backup With:/usr/local/pgsql/bin/psql  -f outputfile
  • by Pg_upgrade upgrade : https://www.postgresql.org/docs/9.6/static/pgupgrade.html
    1. Pg_upgrade-b oldbindir-b newbindir-d olddatadir-d newdatadir [option ...]
    2. ...

Iv. Encryption and Secure

• Preventing Server Spoofing

postgresql[9.4-9.6]--Server Management