PostgreSQL Lock Database để Snapshot, Migrate Server

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.

 

 

SaKuRai

Xin chào, Mình là Sakurai. Blog này là nơi để note lại và chia sẻ những kiến thức, kinh nghiệm mà mình và anh em trong Team. Cảm ơn các bạn đã quan tâm theo dõi!

You may also like...

Leave a Reply