Skip to content

Moving tables to new tablespace

Jump to bottom
adamhooper edited this page Nov 25, 2014 · 6 revisions

[Adam, 2014-11-25]

Requirements

  • Save space on the "old" database volume
  • Zero downtime

Assumptions

  • The "old" database volume is filled mostly by file contents, which we'll be removing as we do this migration
  • Staging will be up the entire time

Approach

  1. Create a new volume on EC2 and mount it
  2. Make Postgres aware of the new volume
  3. Use pg_repack to move tables from the old volume to the new one.

Steps

I ran all these steps on staging/database first, then on production/database.

# We'll need [email protected], because @9.3 will break
echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main 9.1" | sudo tee /etc/apt/sources.list.d/pgdg.list
echo -e "Package: libpq-dev\nPin: version 9.1.*\nPin-Priority: 1001" | sudo tee /etc/apt/preferences.d/libpq-dev
echo -e "Package: libpq5\nPin: version 9.1.*\nPin-Priority: 1001" | sudo tee /etc/apt/preferences.d/libpq5
sudo apt-get updatesudo apt-get install postgresql-server-dev-9.1 build-essential # to compile extension
sudo apt-get install pgxnclient # pgxn installs Postgres extensions
Clone this wiki locally