DBA: changing the "elephants" at the crossing

As normal DBAs, we waited for the release of a couple of minor versions for PostgreSQL 13, which should please us with many useful things , and now we are ready to transfer the database of our monitoring service for this DBMS from version 12 to 13 .

But how to do this with minimal downtime, or better without it at all? The Foreign Data Wrappers functionality will come to the rescue , or rather, postgres_fdw .

Source base structure

Some details about the structure of the base of our service, which help us to very quickly record incoming data, I told in the articles "Writing in PostgreSQL on the sublight: 1 host, 1 day, 1TB" and "Saving a pretty penny on large volumes in PostgreSQL" . In a nutshell, competent database partitioning solves a lot of performance problems.

, 100-150GB . tst



PostgreSQL 12:

CREATE TABLE archive(
  dt
    date
, val
    integer
)
PARTITION BY RANGE(dt); --   

CREATE TABLE archive_20210401 --   
  PARTITION OF archive
    FOR VALUES FROM ('2021-04-01') TO ('2021-04-02');
    -- dt >= '2021-04-01' AND dt < '2021-04-02'

CREATE TABLE archive_20210402
  PARTITION OF archive
    FOR VALUES FROM ('2021-04-02') TO ('2021-04-03');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

dt



, PARTITION BY RANGE



FOR VALUES FROM (dt) TO (dt + 1)



PARTITION BY LIST



, - , .

, :

INSERT INTO archive
VALUES
  ('2021-04-01', 1)
, ('2021-04-02', 2)
RETURNING
  tableoid::regclass
, *;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

 tableoid        |  dt        | val
archive_20210401 | 2021-04-01 |   1
archive_20210402 | 2021-04-02 |   2
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

tableoid



- , ( - ), . "PostgreSQL Antipatterns: ".

""

, , 1-2 "" "" .

PostgreSQL 13 , . , v12 :5439



, v13 :5440



.

:

CREATE TABLE archive(
  dt
    date
, val
    integer
)
PARTITION BY RANGE(dt);

CREATE TABLE archive_20210403
  PARTITION OF archive
    FOR VALUES FROM ('2021-04-03') TO ('2021-04-04');

      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

IMPORT FOREIGN SCHEMA, (, , ...). , , :

CREATE EXTENSION postgres_fdw;

CREATE SERVER postgresql_12
  FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '127.0.0.1', port '5439', dbname 'tst');

CREATE USER MAPPING FOR postgres
  SERVER postgresql_12
    OPTIONS (user 'postgres', password 'postgres');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, :

CREATE FOREIGN TABLE archive_old
  PARTITION OF archive
    FOR VALUES FROM ('-infinity') TO ('2021-04-03')
    -- dt < '2021-04-03'
  SERVER postgresql_12
    OPTIONS(table_name 'archive');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

PARTITION BY RANGE



BY LIST



, .

, :

INSERT INTO archive
VALUES
  ('2021-04-01', 1)
, ('2021-04-02', 2)
, ('2021-04-03', 3)
RETURNING
  tableoid::regclass
, *;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

 tableoid        |  dt        | val
archive_old      | 2021-04-01 |   1 --  
archive_old      | 2021-04-02 |   2
archive_20210403 | 2021-04-03 |   3 --  
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

FDW

- , FDW- updatable



, - use_remote_estimate



fetch_size



.

, FDW-, - PARTITION BY HASH



, .