Situation

Recently we need to life and shift some datasets from AWS Redshift to AWS Aurora in daily basis.

Intuitively I was thinking this progress should be very straightforward, because both Redshift and Aurora are nothing but Postgres variants, and we could utilise all the Postgres toolings (e.g., pg_dump, pg_restore, COPY etc) to transfer the data. But in reality, nothing is hard until you start to implement and write the actual code to do the work.

There are few things that are not been considered at the beginning of this project.

  1. From Redshift side, the source data is dynamically changing quite frequently because of data usage by multiple users.
  2. From Aurora side, there are lots of user operations applying to the data, e.g. creating customised views linked with source data, granting various permissions to different users.
  3. The most important and criterial issue is that the query concurrency scenario is out of scope at the beginning. Schemas and tables will be in Table Lock mode if there is a user query against those table, and hence the data updating process will hang there until the schemas and tables are released.

Task

It seems to me that the table lock problem is the most criterial issue. Luckily we could use the system table pg_locks to detect the current Table Lock information. pg_locks provides a global view of all locks in the database cluster, not only those relevant to the current database. Although its relation column can be joined against pg_class.oid to identify locked relations, this will only work correctly for relations in the current database (those for which the database column is either the current database’s OID or zero).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

prd=> SELECT relation::regclass, locktype, database, relation, pid, mode, granted, fastpath FROM pg_locks;
                    relation                     |  locktype  | database | relation  |  pid  |      mode       | granted | fastpath
-------------------------------------------------+------------+----------+-----------+-------+-----------------+---------+----------
  pg_stat_database                                | relation   |    13934 |     11703 | 54371 | AccessShareLock | t       | t
                                                  | virtualxid |          |           | 54371 | ExclusiveLock   | t       | t
  pg_locks                                        | relation   |    21465 |     11577 | 68877 | AccessShareLock | t       | t
                                                  | virtualxid |          |           | 68877 | ExclusiveLock   | t       | t
  powerusers_xerocard.puxcuc_created              | relation   |    21465 | 351168910 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.puxcuc_usrdeets             | relation   |    21465 | 322042383 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.puxcuc_usrlogin             | relation   |    21465 | 322042377 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.ix_xc_uc_status             | relation   |    21465 | 314731513 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.ix_xc_uc_email              | relation   |    21465 | 314731512 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.ix_xc_uc_userid             | relation   |    21465 | 314731511 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.puxcoc_orgid                | relation   |    21465 | 314731271 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.puxcoc_orgflags             | relation   |    21465 | 314731270 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.ix_xc_userorgrole_role      | relation   |    21465 | 314731528 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.ix_xc_userorgrole           | relation   |    21465 | 314731487 | 64656 | AccessShareLock | t       | t
  digitalmarketing_dbo.google_xerogoogleidlink    | relation   |    21465 | 165584573 | 64656 | AccessShareLock | t       | t
  digitalmarketing_dbo.core_dimuseraccount        | relation   |    21465 | 165585662 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.organisationmilestones      | relation   |    21465 | 307437428 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.usercard                    | relation   |    21465 | 307432249 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.organisationcard            | relation   |    21465 | 307432243 | 64656 | AccessShareLock | t       | t
  powerusers_xerocard.userorganisationrole        | relation   |    21465 | 307437422 | 64656 | AccessShareLock | t       | t
                                                  | virtualxid |          |           | 64656 | ExclusiveLock   | t       | t
  digitalmarketing_dbo."IX_core_dimuseraccount"   | relation   |    21465 | 165594407 | 64656 | AccessShareLock | t       | f
  powerusers_xerocard.puxcom_orgid                | relation   |    21465 | 314731283 | 64656 | AccessShareLock | t       | f
  pg_database_datname_index                       | relation   |        0 |      2671 | 54371 | AccessShareLock | t       | f
  pg_database                                     | relation   |        0 |      1262 | 54371 | AccessShareLock | t       | f
  digitalmarketing_dbo.pk_xerogoogleidlink        | relation   |    21465 | 314752517 | 64656 | AccessShareLock | t       | f
  digitalmarketing_dbo.uq_dimuseraccount_xuser    | relation   |    21465 | 165594411 | 64656 | AccessShareLock | t       | f
  digitalmarketing_dbo.pk_dimuseraccount          | relation   |    21465 | 165594409 | 64656 | AccessShareLock | t       | f
  pg_database_oid_index                           | relation   |        0 |      2672 | 54371 | AccessShareLock | t       | f
  digitalmarketing_dbo.ix_google_xerogoogleidlink | relation   |    21465 | 314752522 | 64656 | AccessShareLock | t       | f

pg_locks provides lots useful information, but here we just choose the following fields

  • locktype. Type of the lockable object: relation, extend, page, tuple, transactionid, virtualxid, object, userlock or advisory
  • database. OID of the database in which the lock target exists, or zero if the target is a shared object, or null if the target is a transaction ID
  • relation. OID of the relation targeted by the lock, or null if the target is not a relation or part of a relation.
  • pid. Process ID of the server process holding or awaiting this lock, or null if the lock is held by a prepared transaction.
  • mode. Name of the lock mode held or desired by this process.
  • granted. True if lock is held, false if lock is awaited
  • fastpath. True if lock was taken via fast path, false if taken via main lock table

Here is an example query to extract all the information we need:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
  psa.usename,
  psa.query_start,
  psa.wait_event_type,
  psa.wait_event,
  psa.state,
  relation::regclass,
  pl.locktype,
  pl.database,
  pl.relation,
  pl.pid,
  pl.mode,
  pl.granted
FROM pg_locks pl
LEFT JOIN pg_stat_activity psa
  ON pl.pid = psa.pid;

We could also use the pg_locks and pg_stat_activity tables to check the query age with the following statement

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
      a.datname as db_name,
      c.relname as relation_name,
      relation::regclass as table_name,
--     l.transactionid,
      l.mode,
      l.GRANTED,
      a.usename,
--     a.query,
      a.query_start,
      age(now(), a.query_start) AS query_age,
      a.pid
FROM  pg_stat_activity a
  JOIN pg_locks         l ON l.pid = a.pid
  JOIN pg_class         c ON c.oid = l.relation
ORDER BY a.query_start;

Result

Eventually, with the help of pg_locks table, we created an automatic ETL pipeline to transfer data from AWS Redshift to AWS Aurora on daily basis.

Hope it helps.

Reference