Migrate databases to postgres 9.5
Closed, MigratedEdits Locked
Actions

Assigned To

Authored By

	olasd
	Jan 18 2016, 5:59 PM

Description

                                         List of databases
                Name                |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
------------------------------------+----------+----------+---------+---------+-----------------------
 antelink                           | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 gitimport_20150930                 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 lister-github                      | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 olasd                              | olasd    | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres                           | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 snapshot.debian.org                | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 softwareheritage                   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +
                                    |          |          |         |         | postgres=CTc/postgres+
                                    |          |          |         |         | olasd=CTc/postgres
 softwareheritage-log               | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 softwareheritage-snapshot-20151001 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 softwareheritage-snapshot-20151006 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 softwareheritage-test-dir          | olasd    | UTF8     | C.UTF-8 | C.UTF-8 | 
 template-swh                       | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0                          | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                                    |          |          |         |         | postgres=CTc/postgres
 template1                          | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                                    |          |          |         |         | postgres=CTc/postgres
 zack                               | zack     | UTF8     | C.UTF-8 | C.UTF-8 | 
 zack-swh-sample                    | zack     | UTF8     | C.UTF-8 | C.UTF-8 |

move plan :

database	destination	status	duration
softwareheritage	9.5/main	done	dump: 6h50; restore 26h04
softwareheritage-log	9.5/secondary	done	4h03
softwareheritage-snapshot-*	9.5/secondary	done	4h30
softwareheritage-test-dir	9.5/secondary	done	33m
zack-*	9.5/secondary	done	12m
olasd	9.5/secondary	done	7h07
gitimport_20150930	9.5/secondary	done	42m
antelink	9.5/hdd	done	1h20
snapshot.debian.org	9.5/hdd	done	31m
lister-github	9.5/hdd	done	1h38

Related Objects
Search...

		Status	Assigned	Task
		Migrated	gitlab-migration	T262 Upgrade postgresql to 9.5
		Migrated	gitlab-migration	T267 Migrate databases to postgres 9.5

Event Timeline

olasd created this task.Jan 18 2016, 5:59 PM

olasd changed the task status from Open to Work in Progress.

olasd raised the priority of this task from to Normal.

olasd updated the task description. (Show Details)

olasd added projects: System administrators, Staff.

olasd added subscribers: zack, olasd.

olasd updated the task description. (Show Details)Jan 18 2016, 6:17 PM

olasd updated the task description. (Show Details)Jan 18 2016, 10:58 PM

olasd updated the task description. (Show Details)Jan 19 2016, 8:25 AM

Database cluster initialization and credentials sync (-g: dump only tablespaces and users):
pg_dumpall -g -p <old db port> | psql -p <new db port>

Dumping and reloading one database (-C: add create database statement):
time pg_dump -p <old db port> -C <dbname> | psql -p <new db port>

We can also use the directory custom dump format to allow for job parallelization, which was used for the softwareheritage database :

Dumping one database in directory format
time pg_dump -Fd -p <old db port> -j <num jobs> -d <database> -f <destination dir>

Restoring one database in directory format
time pg_restore -p <new db port> -d postgres -cC -j <num jobs> <source dir>

We used 16 as the number of jobs, which will restore data and create indexes in parallel.

The directory format dump can also be passed to pg_restore for conversion to a SQL format dump:
pg_restore -v <source dir> | pigz > database.sql.gz

olasd updated the task description. (Show Details)Jan 19 2016, 11:57 AM

olasd updated the task description. (Show Details)Jan 19 2016, 2:30 PM

olasd updated the task description. (Show Details)Jan 19 2016, 4:33 PM

olasd updated the task description. (Show Details)Jan 20 2016, 7:11 AM

olasd updated the task description. (Show Details)Jan 20 2016, 4:51 PM

olasd updated the task description. (Show Details)Jan 20 2016, 10:26 PM

olasd updated the task description. (Show Details)Jan 20 2016, 11:01 PM

olasd closed this task as Resolved.Jan 21 2016, 12:45 AM

olasd claimed this task.

olasd updated the task description. (Show Details)

zack removed projects: Staff, System administrators.Mar 10 2016, 5:51 PM

olasd changed the visibility from "All Users" to "Public (No Login Required)".May 13 2016, 5:08 PM