postgres configuration postgresql.conf - ghdrako/doc_snipets GitHub Wiki

obraz

File in the $PGDATA directory. There are over 350 configurable parameters for the PostgreSQL database.

At a minimum, you have to add values for the following:

  1. Database port (the default values is 5432)
  2. Listener address to “*” values, which allow client access from any host
  3. wal_level replica for replication
  4. archive_mode to copy log files to backup
  5. archive for the destination of archived log files
  6. Enable logging
  7. log directory
# postgresql.conf entry
# make directory for continued archive log storage
mkdir -p $PGDATA/archive
echo "port = 5432" >>$PGDATA/postgresql.conf
# enable access from all hosts
echo "listen_addresses = '*'" >>$PGDATA/postgresql.conf
echo "wal_level = replica" >>$PGDATA/postgresql.conf
# enables archiving on
echo "archive_mode =on" >>$PGDATA/postgresql.conf
# specify archive path
echo "archive_command = 'test ! -f $PGDATA/archive/%f && cp %p
$PGDATA/archive/%f'" >> $PGDATA/postgresql.conf
# enable logging
echo "logging_collector = on" >>$PGDATA/postgresql.conf
# log directory
echo "log_directory = 'log'" >>$PGDATA/postgresql.conf

Changing parameters in file

$sed -i 's/shared_buffers = 128MB/shared_buffers = 1024MB/g' $PGDATA/postgresql.conf
$grep shared_buffers $PGDATA/postgresql.conf

pg_ctl restart -D $PGDATA
psql -c " show  shared_buffers;"

Modify Parameters with the Alter System

psql -c  "alter system set shared_buffers to '1024MB';"

Note the changes made by the alter system command are registered in the postgresql.auto.conf file, which is used during the restart of the postgresQL cluster.