PostgreSQL Lock Database để Snapshot, Migrate Server
Trong quá trình vận hành cụm Cluster PostgreSQL có trường hợp cần phải migrate hoặc snapshot cụm DB các bước tiến hành như sau
Truy cập vào server master
su - postgres
psql -u root -p -h 127.0.0.1 -p5432
LOCK DATABASE
LOCK DATABASE is a database-level lock mechanism. This functionality is not implemented in PostgreSQL, and is not planned to be yet.
Synopsis
LOCK DATABASE db_name [IN dblockmode MODE] [NOWAIT]; UNLOCK DATABASE db_name;
dblockmode having the following options: SHARE | EXCLUSIVE
EXCLUSIVE excludes all SQL query to be run on the database. No connection is allowed from external applications. SHARE authorizes SELECT queries and connections to the database. DDL and DML are blocked.
Characteristics
LOCK DATABASE is a database-level lock, waiting for any locks to be released before taking it. For example, if lock is taken on a table of this database, LOCK DATABASE waits until it is released. When waiting for locking, new table locks of this database cannot be taken.
It is also possible to set NOWAIT what makes LOCK DATABASE to return an error if lock cannot be taken immediately. mechanism to ensure that no transaction can be run on this database or no user can access to this database when lock is taken.
Lock can be released with command UNLOCK DATABASE or when session that used this database is disconnected. PostgreSQL always takes the least level of lock when using such mechanism. LOCK DATABASE can be used if database administrator or superuser needs more restrictive lock mechanism.
Như vậy khóa DB câu lệnh như sau
LOCK DATABASE testdb NOWAIT;
#Để mở khóa
UNLOCK DATABASE testdb;
Usage
Server maintenance:
instead of taking multiple locks on tables of a database.
Cluster of nodes:
PostgreSQL does not ensure a DROP DATABASE query if done on multiple nodes at the same time as query is autocommit. DROP DATABASE can easily fail on a node if an application keeps a connection alive on it. However DROP succeeds if no session are alive on this node of the cluster. In this case LOCK DATABASE can be used as a 2PC like mechanism to ensure database drop in a cluster of nodes.
