Project

General

Profile

Task #11369

chimera database backup, dump and restore.

Added by Chih-Hao Huang almost 4 years ago. Updated almost 4 years ago.

Status:
Resolved
Priority:
Normal
Start date:
01/07/2016
Due date:
01/14/2016
% Done:

100%

Estimated time:
16.00 h
Spent time:
Duration: 8

Description

The goal is to come up with full backup of chimera database to tape.
This task includes:
  1. measure the cost (size and time) of dumping and restoring of current production databases
  2. fix apparently double mounting same disk partitions, /var/lib/pgsql and /var/lib/pgsql-puppet on both production backup servers
  3. have a cronjob to do database dumps and store them on tapes

History

#1 Updated by Chih-Hao Huang almost 4 years ago

Since production databases on cmschimera and cmschimeradisk are accessible from their backup counterparts, cmschimerabackup and cmschimeradiskbackup, and they have the identical hardware, the backup nodes are used for the test environment.
  1. do pg_dump of the production database over the network from the backup node, the dump file land in backup node
  2. create a separate test database area (different port, apart from the slave) on the backup node
  3. restore the dump to the test database
  4. measure the time and relevant sizes

Current database statistics:

Database sizes on cmschimera and cmschimerabackup

bash-4.1$ ssh -l root cmschimera df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3       259G  155G   91G  64% /
tmpfs            64G     0   64G   0% /dev/shm
/dev/sda1       976M   93M  832M  11% /boot
/dev/sdb1       2.0T  227G  1.7T  12% /var/lib/pgsql-puppet
bash-4.1$ ssh -l root cmschimerabackup df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3       259G   85G  161G  35% /
tmpfs            64G     0   64G   0% /dev/shm
/dev/sda1       976M   93M  833M  11% /boot
/dev/sdb1       2.0T   85G  1.8T   5% /var/lib/pgsql
/dev/sdb1       2.0T   85G  1.8T   5% /var/lib/pgsql-puppet

227G to 85G (quite a lot of garbage in the master)

Database sizes on cmschimeradisk and cmschimeradiskbackup:

bash-4.1$ ssh -l root cmschimeradisk df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3       259G  113G  133G  46% /
tmpfs            64G     0   64G   0% /dev/shm
/dev/sda1       976M   93M  833M  11% /boot
/dev/sdb1       2.0T   34G  1.9T   2% /var/lib/pgsql-puppet
localhost:/     1.0E  9.6P 1015P   1% /dcache
bash-4.1$ ssh -l root cmschimeradiskbackup df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3       259G   93G  153G  38% /
tmpfs            64G     0   64G   0% /dev/shm
/dev/sda1       976M   93M  833M  11% /boot
/dev/sdb1       2.0T   26G  1.9T   2% /var/lib/pgsql
/dev/sdb1       2.0T   26G  1.9T   2% /var/lib/pgsql-puppet

34G to 26G

#2 Updated by Chih-Hao Huang almost 4 years ago

  • % Done changed from 0 to 20

For cmschimera (tape):

[root@cmschimerabackup HUANGCH]# touch start_dump; /usr/pgsql-9.2/bin/pg_dump -h cmschimera -U enstore chimera -F c -f chimera-tape.dmp; touch finish_dump
[root@cmschimerabackup HUANGCH]# ls -lh
total 5.0G
drwxr-xr-x 2 root root 4.0K Jan  7 13:45 chimeradb
-rw-r--r-- 1 root root 5.0G Jan  7 14:44 chimera-tape.dmp
-rw-r--r-- 1 root root    0 Jan  7 14:44 finish_dump
-rw-r--r-- 1 root root    0 Jan  7 14:24 start_dump

It took 20 minutes to dump. The size of dump file, compressed, is 5GB (comparing to 227GB of database area).

For cmschimeradisk (disk):

[root@cmschimeradiskbackup HUANGCH]# touch start_dump; /usr/pgsql-9.2/bin/pg_dump -h cmschimeradisk -U enstore chimera -F c -f chimera-disk.dmp; touch finish_dump
[root@cmschimeradiskbackup HUANGCH]# ls -lh
total 1.1G
drwxr-xr-x 2 root root 4.0K Jan  7 13:45 chimeradb
-rw-r--r-- 1 root root 1.1G Jan  7 14:28 chimera-disk.dmp
-rw-r--r-- 1 root root    0 Jan  7 14:28 finish_dump
-rw-r--r-- 1 root root    0 Jan  7 14:24 start_dump

It took 4 minutes to dump. The size of dump file, compressed, is 1.1GB (comparing to 34GB of database area).

#3 Updated by Chih-Hao Huang almost 4 years ago

The process is the same on both.
  1. postgresql can not run as root -- user postgres is the only choice here
  2. since the production slave is running at default port 5432, need to choose different port -- choose 8000

Create area for database that user postgres can write

[root@cmschimerabackup HUANGCH]# mkdir /home/postgres
[root@cmschimerabackup HUANGCH]# chown postgres.postgres /home/postgres
[root@cmschimerabackup HUANGCH]#

Initialize database area
[root@cmschimerabackup HUANGCH]# su postgres
bash-4.1$ /usr/pgsql-9.2/bin/initdb -D /home/postgres/chimeradb
could not change directory to "/root/HUANGCH" 
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

creating directory /home/postgres/chimeradb ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /home/postgres/chimeradb/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-9.2/bin/postgres -D /home/postgres/chimeradb
or
    /usr/pgsql-9.2/bin/pg_ctl -D /home/postgres/chimeradb -l logfile start

bash-4.1$

Starting database server
bash-4.1$ /usr/pgsql-9.2/bin/postgres -D /home/postgres/chimeradb -p 8000&
[1] 26559
bash-4.1$

Checking database server
bash-4.1$ ps aux | grep "/home/postgres/chimeradb" | grep -v grep
postgres 26559  0.0  0.0 219888  8576 pts/2    S    17:14   0:00 /usr/pgsql-9.2/bin/postgres -D /home/postgres/chimeradb -p 8000
bash-4.1$ 

#4 Updated by Chih-Hao Huang almost 4 years ago

Create user enstore and database chimera.

[root@cmschimeradiskbackup postgres]# psql -U postgres template1 -p 8000
psql (9.2.6)
Type "help" for help.

template1=# \du+
                                    List of roles
 Role name |                   Attributes                   | Member of | Descri
ption 
-----------+------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication | {}        | 

template1=# create user enstore superuser createdb;
CREATE ROLE
template1=# create database chimera;
CREATE DATABASE
template1=# \du+
                                    List of roles
 Role name |                   Attributes                   | Member of | Description 
-----------+------------------------------------------------+-----------+-------------
 enstore   | Superuser, Create DB                           | {}        | 
 postgres  | Superuser, Create role, Create DB, Replication | {}        | 

template1=# \q
[root@cmschimeradiskbackup postgres]# psql -U enstore template1 -p 8000
psql (9.2.6)
Type "help" for help.

template1=# create database chimera;
CREATE DATABASE
template1=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 chimera   | enstore  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

template1=# 

#5 Updated by Chih-Hao Huang almost 4 years ago

[root@cmschimeradiskbackup ~/HUANGCH]# touch start_restore; /usr/pgsql-9.2/bin/pg_restore -p 8000 -U enstore -d chimera chimera-disk.dmp; touch finish_restore & 
[1] 6241
[root@cmschimeradiskbackup ~/HUANGCH]# 
[root@cmschimeradiskbackup ~/HUANGCH]# pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2985; 0 0 ACL t_inodes_checksum enstore
pg_restore: [archiver (db)] could not execute query: ERROR:  role "enstore_reader" does not exist
    Command was: REVOKE ALL ON TABLE t_inodes_checksum FROM PUBLIC;
REVOKE ALL ON TABLE t_inodes_checksum FROM enstore;
GRANT ALL ON TABLE t_...
pg_restore: [archiver (db)] Error from TOC entry 2986; 0 0 ACL t_locationinfo enstore
pg_restore: [archiver (db)] could not execute query: ERROR:  role "enstore_reader" does not exist
    Command was: REVOKE ALL ON TABLE t_locationinfo FROM PUBLIC;
REVOKE ALL ON TABLE t_locationinfo FROM enstore;
GRANT ALL ON TABLE t_locati...
WARNING: errors ignored on restore: 2

[1]    Done                          ( touch start_restore; /usr/pgsql-9.2/bin/pg_restore -p 8000 -U enstore -d chimera chimera-disk.dmp; touch finish_restore )
[root@cmschimeradiskbackup ~/HUANGCH]# ls -l
total 1054504
-rw-r--r-- 1 root root 1079799898 Jan  7 14:28 chimera-disk.dmp
drwxr-xr-x 2 root root       4096 Nov  6 15:31 CMSSTOR263
-rw-r--r-- 1 root root          0 Jan  7 14:28 finish_dump
-rw-r--r-- 1 root root          0 Jan  8 08:29 finish_restore
-rw-r--r-- 1 root root          0 Jan  7 14:24 start_dump
-rw-r--r-- 1 root root          0 Jan  8 08:01 start_restore
[root@cmschimeradiskbackup ~/HUANGCH]# stat start_restore finish_restore | grep Change
Change: 2016-01-08 08:01:11.239567109 -0600
Change: 2016-01-08 08:29:17.829322685 -0600
[root@cmschimeradiskbackup ~/HUANGCH]# du -sh /home/postgres/chimeradb
9.3G    /home/postgres/chimeradb
  1. The error came from missing role of enstore_reader, which is fine and does not impact this test.
  2. It took 28 minutes and 7 seconds to restore
  3. restored database area size is 9.3GB

#6 Updated by Chih-Hao Huang almost 4 years ago

  • % Done changed from 20 to 50
[root@cmschimerabackup ~/HUANGCH]# touch start_restore; /usr/pgsql-9.2/bin/pg_restore -p 8000 -U enstore -d chimera chimera-tape.dmp; touch finish_restore &
[1] 8544
[root@cmschimerabackup ~/HUANGCH]# pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2971; 0 0 ACL public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  role "enstore_reader" does not exist
    Command was: REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
...
pg_restore: [archiver (db)] Error from TOC entry 2973; 0 0 ACL t_inodes_checksum enstore
pg_restore: [archiver (db)] could not execute query: ERROR:  role "enstore_reader" does not exist
    Command was: REVOKE ALL ON TABLE t_inodes_checksum FROM PUBLIC;
REVOKE ALL ON TABLE t_inodes_checksum FROM enstore;
GRANT ALL ON TABLE t_...
pg_restore: [archiver (db)] Error from TOC entry 2974; 0 0 ACL t_locationinfo enstore
pg_restore: [archiver (db)] could not execute query: ERROR:  role "enstore_reader" does not exist
    Command was: REVOKE ALL ON TABLE t_locationinfo FROM PUBLIC;
REVOKE ALL ON TABLE t_locationinfo FROM enstore;
GRANT ALL ON TABLE t_locati...
pg_restore: [archiver (db)] Error from TOC entry 2975; 0 0 ACL t_locationinfo_trash enstore
pg_restore: [archiver (db)] could not execute query: ERROR:  role "enstore_reader" does not exist
    Command was: REVOKE ALL ON TABLE t_locationinfo_trash FROM PUBLIC;
REVOKE ALL ON TABLE t_locationinfo_trash FROM enstore;
GRANT ALL ON TA...
WARNING: errors ignored on restore: 4

[1]    Done                          ( touch start_restore; /usr/pgsql-9.2/bin/pg_restore -p 8000 -U enstore -d chimera chimera-tape.dmp; touch finish_restore )
[root@cmschimerabackup ~/HUANGCH]# stat start_restore finish_restore | grep Change
Change: 2016-01-08 08:04:05.510726043 -0600
Change: 2016-01-08 10:33:07.297109812 -0600
[root@cmschimerabackup ~/HUANGCH]# du -hs /home/postgres/chimeradb/
60G    /home/postgres/chimeradb/
[root@cmschimerabackup ~/HUANGCH]# 
  1. Errors can be ignored
  2. It took 2 hours, 29 minutes to restore
  3. Restored database area size is 60GB

#7 Updated by Chih-Hao Huang almost 4 years ago

[root@cmschimerabackup ~]# psql -U enstore -h cmschimera chimera
psql (9.2.6)
Type "help" for help.

chimera=> select count(*) from t_inodes where itype = 32768;
  count   
----------
 19105627
(1 row)

chimera=> \q
[root@cmschimerabackup ~]#

[root@cmschimeradiskbackup ~]# psql -U enstore -h cmschimeradisk chimera
psql (9.2.6)
Type "help" for help.

chimera=> select count(*) from t_inodes where itype = 32768;
  count  
---------
 4077846
(1 row)

chimera=> \q
[root@cmschimeradiskbackup ~]# 
  1. number of files in disk instance: 4077846
  2. number of files in tape instance: 19105627

#8 Updated by Chih-Hao Huang almost 4 years ago

  • % Done changed from 50 to 60

Previous database area sizes were calculated from the sizes of partitions.
cmschimera:/var/lib/pgsql-puppet does contains other unrelated files left from last disk-tape separation.
Here is a recalculation of only the database data areas.

[root@cmschimera pgsql-puppet]# du -hs /var/lib/pgsql-puppet/data
82G    /var/lib/pgsql-puppet/data

[root@cmschimerabackup pgsql-puppet]# du -hs /var/lib/pgsql-puppet/data /home/postgres/chimeradb/
82G    /var/lib/pgsql-puppet/data
60G    /home/postgres/chimeradb/

[root@cmschimeradisk ~]# du -sh /var/lib/pgsql-puppet/data
25G    /var/lib/pgsql-puppet/data

[root@cmschimeradiskbackup ~]# du -hs /var/lib/pgsql/9.2/data /home/postgres/chimeradb/
26G    /var/lib/pgsql/9.2/data
9.3G    /home/postgres/chimeradb/

#9 Updated by Chih-Hao Huang almost 4 years ago

  • Status changed from Assigned to Resolved
  • % Done changed from 60 to 100

chimera database dumps are taken and written to enstore 4 times a day.



Also available in: Atom PDF