Moving tables to new tablespace

[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.

Step 1: install pg_repack

# We'll need libpq5-dev@9.1, 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
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo add-apt-repository ppa:ubuntu-toolchain-r/test # for GCC 4.8, on Ubuntu Precise
sudo apt-get update
sudo apt-get install postgresql-server-dev-9.1 build-essential libedit-dev # to compile extension
sudo apt-get install pgxnclient # pgxn installs Postgres extensions
sudo apt-get install gcc-4.8 # on Ubuntu Precise
sudo update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-4.8 50 # on Ubuntu Precise
sudo pgxn install pg_repack
sudo -u postgres psql overview -c 'CREATE EXTENSION pg_repack'

Step 2: add new volume

1. Create a volume named database-main, 500GB SSD
2. Attach it to the "database" instance, as /dev/sdg
3. Run the following:

sudo mkfs.ext4 /dev/xvdg
echo "/dev/xvdg /var/lib/pgdata-main auto defaults 0 0" | sudo tee -a /etc/fstab
sudo mkdir /var/lib/pgdata-main
sudo chown postgres:postgres /var/lib/pgdata-main
sudo mount /var/lib/pgdata-main
sudo chown postgres:postgres /var/lib/pgdata-main -R

Step 3: make it a tablespace

sudo -u postgres psql -c "CREATE TABLESPACE main LOCATION '/var/lib/pgdata-main'"

Step 3: move tables

sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=view --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=api_token --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=play_evolutions --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=file_group --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=uploaded_file --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=document_set_creation_job --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=store_object --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=document_set --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=document_set_user --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=session --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=tree --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=temp_document_set_file --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=search_result --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=tag --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=user --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=document_processing_error --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=grouped_file_upload --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=file --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=document_search_result --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=document_tag --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=node --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=node_document --tablespace=main --moveidx overview
sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_repack --table=document --tablespace=main --moveidx overview