Posts Tagged ‘postgresql’

How to dump and restore Zabbix database to do monitoring database snapshot, PostGreSQL continuous archiving Point in time recovery setup

Monday, November 21st, 2022

postgresql-db-backup-and-continuous-archiving-on-linux-keep-zabbix-backup-regularly

If you have set up a Zabbix server that is using as a database storage postgresql at home or company, then you will need to make sure you don’t loose any data by producing regular postgresql backups. This small article will show how to do the db backup in conventional way with pg_dump / pg_restore as well as how to set the continuous archiving point in time recovery end point recovery so called PITR..
 

1. Connect to the database and some postgres very basics

-bash-4.2$ psql DBNAME USERNAME
 
-bash-4.2$ psql zabbix zabbix
 

After you access a PostgreSQL database, you can run SQL queries and more. Here are some common psql commands:
 
•    To view help for psql commands, type \?.
•    To view help for SQL commands, type \h.
•    To view information about the current database connection, type \conninfo.
•    To list the database's tables and their respective owners, type \dt.
•    To list all of the tables, views, and sequences in the database, type \z.
•    To exit the psql program, type \q.

       Fundamental backup approaches with postgres

As with everything that contains valuable data, PostgreSQL databases should be backed up regularly. While the procedure is essentially simple, it is important to have a clear understanding of the underlying techniques and assumptions.
 
There are three fundamentally different approaches to backing up PostgreSQL data:
 
•    SQL dump
•    File system level backup
•    Continuous archiving

 
Each has its own strengths and weaknesses; each is discussed in turn in the following sections.
 

2.    Creating SQL Dump of postgresql target database

2.1 Manual SQL dump (custom dump format)

 
Use pg_dump's custom dump format. If PostgreSQL was built on a system with the zlib compression library installed, the custom dump format will compress data as it writes it to the output file.
This will produce dump file sizes similar to using gzip, but it has the added advantage that tables can be restored selectively.
The following command dumps a database using the custom dump format:
 

# sudo su – postgres
-bash-4.2% cd  /var/lib/pgsql/9.5/backups/
 
-bash-4.2$  pg_dump -Fc zabbix > /var/lib/pgsql/9.5/backups/zabbixdbdump`date "+%d%m%y%H%M%S"`.gz

2.2 Schedule backups with cron job

To automate the job schedule a cron job to do the work, somethjing like below:
 

# Minute   Hour   Day of Month       Month          Day of Week        Command
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)

 
@daily  pg_dump -Fc zabbix > /var/lib/pgsql/9.5/backups/Zabbix_db_dump`date "+\%d\%m\%y\%H\%M\%S"`.gz

3. Restore a database from the dump file

3.1. Restoring a database using pg_restore (usually used)

A custom-format dump is not a script for psql, but instead must be restored with pg_restore, for example:
 

# pg_restore -d zabbix /var/lib/pgsql/9.5/backups/Zabbix_db_dump281118151005.gz

3.2. Second Option restoing with psql after creating a restore database

 
# su postgres

-bash-4.2$ psql template1
 
CREATE DATABASE zabbix OWNER zabbix ENCODING 'UTF8';
\q

-bash-4.2$ psql zabbix < /var/lib/pgsql/9.5/backups/zabbixdbdump291117151002.gz
exit

 
NOTE: if you get a permission denied error when trying to restore, check the Unix permissions on the backup file and all the parent directories.

4.    Continuous Archiving and Point-in-Time Recovery (PITR) backups

At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's data directory. The log records every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by "replaying" the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the file system backup and then replay from the backed-up WAL files to bring the system to a current state. This approach is more complex to administer than either of the previous approaches, but it has some significant benefits:

           Setting Up the WAL Archiving

In an abstract sense, a running PostgreSQL system produces an indefinitely long sequence of WAL records. The system physically divides this sequence into WAL segment files, which are normally 16MB a piece (although the segment size can be altered when building PostgreSQL). The segment files are given numeric names that reflect their position in the abstract WAL sequence. When not using WAL archiving, the system normally creates just a few segment files and then "recycles" them by renaming no-longer-needed segment files to higher segment numbers. It's assumed that segment files whose contents precede the checkpoint-before-last are no longer of interest and can be recycled.
 
When archiving WAL data, we need to capture the contents of each segment file once it is filled, and save that data somewhere before the segment file is recycled for reuse.
Depending on the application and the available hardware, there could be many different ways of "saving the data somewhere": we could copy the segment files to an NFS-mounted directory on another machine, write them onto a tape drive (ensuring that you have a way of identifying the original name of each file), or batch them together and burn them onto CDs, or something else entirely. To provide the database administrator with flexibility, PostgreSQL tries not to make any assumptions about how the archiving will be done. Instead, PostgreSQL lets the administrator specify a shell command to be executed to copy a completed segment file to wherever it needs to go. The command could be as simple as a cp, or it could invoke a complex shell script — it's all up to you.
 
To enable WAL archiving, set the wal_level configuration parameter to archive or higher, archive_mode to on, and specify the shell command to use in the archive_command configuration parameter.

In practice these settings will always be placed in the postgresql.conf e.g. (/etc/postgresql/12/main/postgresql.conf) file.

In archive_command, %p is replaced by the path name of the file to archive, while %f is replaced by only the file name. (The path name is relative to the current working directory, i.e., the cluster's data directory.)

Use %% if you need to embed an actual % character in the command.

The simplest useful command to enable PITR would, be something like:
 

# – Archiving config setction
 
archive_mode = on                    # enables archiving; off, on, or always
# (change requires restart)
 
archive_command = '/bin/xz -2 -z < %p > /var/lib/pgsql/9.5/archivedir/%f'               # command to use to archive a logfile segment
 
archive_timeout = 1h            # force a logfile segment switch after this
                                                   # number of seconds; 0 disables

Install and make Apache + PHP to work with PosgreSQL database server on Debian Linux and set up server Web Posgre interface Pgpadmin howto

Wednesday, June 15th, 2016

make-apache-php-work-with-postgresql-pgsql-and-install-postgresql-db-web-admin-interface

In previous article I've wrote on how to install postgresql on Debian Linux using the deb repository this was necessery to import some PostGres DBs, however this was not enough to run the posgresql php based website aimed as connection from Apache / PHP module to PostGre was failing after a bit of investigation and a check in phpinfo(); I've realized the module PHP module for postgres pgsql.so was missing, here is what I did in order to install it:
 

debian:~# apt-get install php5-pgsql phppgadmin libapache2-mod-auth-pgsql 

PHP sessions enable configuration

As it is common a common problem with PHP applications written to use PostGres is to loose sessions and by default PHP does not have configured sessions.save_path it is a very good practice to directly enable it in /etc/php5/apache2/php.ini open the file in text editor:
 

debian:~# vim /etc/php5/apache2/php.ini


Find the commented directive line:
 

;session.save_path = “/tmp”


and uncomment it, i.e.:
 

session.save_path = “/tmp”


Quit saving vim with the usual :wq!

The 3 modules provides pgsql.so for PHP and mod_auth_pgsql.so for Apache2, the 3rd packae phpgadmin provides a Web administration interface for installed PostgreSQL servers Databases, for those experienced with MySQL Database its the same as PHPMyAdmin.

 

 Here is quick configuration for use of PostgreAdmin interface:

By default PHPPGADMIN installation process configure the Apache2 server' /etc/phppgadmin/apache.conf  to use  /etc/apache2/conf.d/phppgadmin


Here is the default my server package instaleld  file content:

 

Alias /phppgadmin /usr/share/phppgadmin

<Directory /usr/share/phppgadmin>

DirectoryIndex index.php
AllowOverride None

order deny,allow
deny from all
allow from 127.0.0.0/255.0.0.0 ::1/128
# allow from all

<IfModule mod_php5.c>
  php_flag magic_quotes_gpc Off
  php_flag track_vars On
  #php_value include_path .
</IfModule>
<IfModule !mod_php5.c>
  <IfModule mod_actions.c>
    <IfModule mod_cgi.c>
      AddType application/x-httpd-php .php
      Action application/x-httpd-php /cgi-bin/php
    </IfModule>
    <IfModule mod_cgid.c>
      AddType application/x-httpd-php .php
      Action application/x-httpd-php /cgi-bin/php
    </IfModule>
  </IfModule>
</IfModule>

</Directory>

It is generally a good practice to change the default Alias location of phppgadmin, so edit the file and change it to something like:
 

Alias /phppostgresgadmin /usr/share/phppgadmin

 

  • Then phpPgAdmin is available at http://servername.com/phppostgresadmin (only from localhost, however in my case I wanted to be able to access it also from other hosts so allowed PostgresGadmin from every hosts, to do so, I've commented in above config

 

# allow from 127.0.0.0/255.0.0.0 ::1/128

 

and uncommented #allow from all line, e.g.:
 

allow from all


Also another thing here is in your VirtualHost whenever you plan to access the PHPPGADMIN is to include in config ( in my case this is the file /etc/apache2/sites-enabled/000-default before (</VirtualHost> end line) following Alias:
 

Alias /phpposgreadmin /usr/share/phppgadmin


Then to access PostGreSQL PHP Admin interface in Firefox / Chrome open URL:

 

http://your-default-domain.com/phpposgreadmin

phpPgAdmin-postgresql-php-web-interface-debian-linux-screenshot
 

 

Configure access to a remote PostgreSQL Server

With PhpPgAdmin, you can manage many PostgreSQL servers locally (on the localhost) or on remote hosts.

First, you have to make sure that the distant PostgreSQL server can handle your request, that you can connect to it. You can do this by modifying the /etc/postgresql/9.5/main/filepg_hba.conf and adding a line like:

# PhpPgAdmin server access host all db_admin xx.xx.xx.xx 255.255.255.255 md5

Then, you need to add your distant PostgreSQL server into the config file for PhpPgAdmin. This file is  /etc/phppgadmin/config.inc.php the default postgresql port is 5432, however you might have configured it already to use some different port if you're not sure about the port number the postgresql is listening check it out:

 

debian:~# grep -i port /etc/postgresql/*/main/postgresql.conf
etc/postgresql/9.5/main/postgresql.conf:port = 5433                # (change requires restart)
/etc/postgresql/9.5/main/postgresql.conf:                    # supported by the operating system:
/etc/postgresql/9.5/main/postgresql.conf:                    # supported by the operating system:
/etc/postgresql/9.5/main/postgresql.conf:# ERROR REPORTING AND LOGGING


To login to phppgadmin interface there is no root administrator user such as in PHP so you will need to priorly create some user and later use it for connection from Postgres Web interface.

To create from console new user in postgres:
 

debian:~# su – postgres
posgres@debian:~$ psql template1
posgres@debian:~$ psql -d template1 -U postgres

 

Welcome to psql 9.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=#

template1=# CREATE USER MyNewUser WITH PASSWORD 'myPassword';


To add a new database to postgres from shell:

template1=# CREATE DATABASE NewDatabase;
 

template1=# GRANT ALL PRIVILEGES ON DATABASE NewDatabase to MyNewUser;

 

template1=# q

Last command instructs it to quit if you want to get more info about possible commands do type instead of q ? for general help or for database / table commands only h
If you need to connect to NewDatabase just to test first it works in console before trying it from postgrepgadmin

 

 

 

 

 

posgres@debian:~$ psql -d NewDatabase -U MyNewUser

 

 

 

 

Install postgresql on Debian Squeeze / How to install PostGreSQL on Obsolete Debian installation

Friday, June 10th, 2016

how-to-install-postgresql-on-obsolete-old-debian-squeeze-tutorial

If you're in position like me to be running an old version of Debian (Squeeze) and you need to install PostgreSQL you will notice that the Debian 6.0 standard repositories are no longer active and apt-get update && apt-get upgrade are returning errors, thus because this Debian release is already too old and even the LTS repositories are inactive it is impossible to install postgresql with the usual.

To get around the situation first thing I did was to try to add followin Debian  repositories. to /etc/apt/sources.list
 

deb http://ftp.debian.net/debian-backports squeeze-backports-sloppy main
deb http://archive.debian.org/debian-archive/debian/ squeeze main contrib non-free
deb http://archive.debian.org/debian-archive/debian/ squeeze-lts main contrib non-free

After adding it I continued getting missing package errors while trying:
 

# apt-get update && apt-get install postgresql postgresql-client
….
…..

 

E: Some index files failed to download. They have been ignored, or old ones used instead.


Thus I googled a bit and I found the following PostgreSQL instructions working Debian 7.0 Wheeze and decided to try it 1 in 1 just changing the repository package wheezy word with squeeze
in original tutorial postgre's deb repositories are:

 

deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main


I've only changed that one with:

 

deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main

 

I guess though this worked for Debian Squeeze installing current versions such as Debian 8.0 Jessis and newer wouldn't be a prolem if you just change the debian version keyword witht he distribution for which you need the postgresql package


Here is all the consequential steps I took to make the PostgreSQL 9.5 running on my old and unsupported Debian 6.0 Squeeze

Create /etc/apt/sources.list.d/pgdg.list. The distributions are called codename-pgdg. In the example, replace wheezy with the actual distribution you are using:

# vim /etc/apt/sources.list.d/pgdg.list

 

deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main

debian:~# apt-get –yes install wget ca-certificates debian:~# wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add – debian:~# apt-get update debian:~# apt-get upgrade debian:~# apt-get –yes install postgresql-9.5 pgadmin3

Next step is to connect to PostGreSQL and create database user and a database # su – postgres $ psql

Create a new database user and a database:

postgres=# CREATE USER mypguser WITH PASSWORD 'mypguserpass'; postgres=# CREATE DATABASE mypgdatabase OWNER mypguser;

 

or

# createuser mypguser #from regular shell # createdb -O mypguser mypgdatabase

Quit from the database

postgres=# q

Connect as user mypguser to new database

# su – mypguser $ psql mypgdatabase

or

# psql -d mypgdatabase -U mypguser

If you get errors like:

psql: FATAL: Ident authentication failed for user "mypguser"

edit pg_hba.conf in /etc/postgresql/9.5.Y/main/pg_hba.conf

 

local all all trust # replace ident or peer with trust

reload postgresql

/etc/init.d/postgresql reload …

 


To make sure that PostGreSQL is running on the system check the following processes are present on the server:

 

 

 

root@pcfreak:/var/www/images# ps axu|grep -i post postgres 9893 0.0 0.0 318696 16172 ? S 15:20 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf postgres 9895 0.0 0.0 318696 1768 ? Ss 15:20 0:00 postgres: checkpointer process postgres 9896 0.0 0.0 318696 2700 ? Ss 15:20 0:00 postgres: writer process postgres 9897 0.0 0.0 318696 1708 ? Ss 15:20 0:00 postgres: wal writer process postgres 9898 0.0 0.0 319132 2564 ? Ss 15:20 0:00 postgres: autovacuum launcher process postgres 9899 0.0 0.0 173680 1652 ? Ss 15:20 0:00 postgres: stats collector process root 14117 0.0 0.0 112404 924 pts/1 S+ 16:09 0:00 grep -i post

 

 


Well that's all folks now you will have the postgresql running on its default port 5433:

 

debian:/etc/postgresql/9.5/main# grep -i port postgresql.conf
port = 5433 # (change requires restart)
# supported by the operating system:
# supported by the operating system:
# ERROR REPORTING AND LOGGING # %r = remote host and port

 

 


Well that's it folks thanks The Lord Jesus Christ grace by the prayers of John The Baptist and Saint Sergij Radonezhki it works 🙂