Re: Partitioning an existing table - pg10.6

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Wed, 3 Jul 2019 11:35:01 +0300



    On 3/7/19 10:01 π.μ., Ayub M wrote:

    
      Hello, I am using postgres 10.6 and have a huge
        table with ~30m recs which needs to be partitioned. Whats the
        best strategy for it with minimal downtime?
        

        1. Rename existing table and create new partitioned tables
          with orig name and keep inserting data from the old renamed to
          new table. This will incur downtime for the apps.
        2. Create partitioned table with new name and move the data
          from current table (move oldest to newest) and when moving
          current month paritition shutdown down the app and move the
          last set of data. This will not incur downtime but if any data
          is changed which was already copied over then it might be
          lost.
      
    
    Regarding 2) you could setup a trigger on the original table which
    would also replicate INSERTS, DELETES and UPDATES to the new table.

    
        Any other approaches/alternatives?

          
          -- 

          Regards,
            Ayub
          
        
    -- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt