-
Notifications
You must be signed in to change notification settings - Fork 3
PostgreSQL
We use PostgreSQL 9.2 with the hstore and tablefunc extensions and 9.2 is the minimum supported version (we use data modifying recursive CTEs as well as some of 9.2 syntax improvements). Everything should work on 9.3 as well and we are planning to use some 9.3 specific features in the future.
The hstore and tablefunc extensions need to be installed separately from the contrib package.
To load the modules into your db:
CREATE EXTENSION hstore
CREATE EXTENSION tablefunc
This is done in migrations for your development and test environments. For staging and production needs to be done by hand, as it requires superuser privileges.
Stored procedures and views are not updated with regular migrations, as that approach was cumbersome. Instead, procedures and other plpgsql constructs are defined in separate files under db/plpgsql and db/views and are hooked to the db:migrate task, so that every time db:migrate runs those files are executed in lexicographic order. Therefore, it is important to use 'CREATE OR REPLACE' or 'DROP' / 'CREATE' in these files for migrations to pass.
These how-tos have been migrated to https://github.com/unepwcmc/wiki/wiki/PostgreSQL
pg_dump myDB | bzip2 -c > myDump.sql.bz2
bzip2 -dc myDump.sql.bz2 | psql -U myUser myDB
(-d option for bzip2 allows piping)
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
When connected to a database, will close all connections except the current.
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();
WITH users_to_obfuscate AS (SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) AS row_no FROM users)
UPDATE users
SET email = 'user+' || row_no || '@example.com'
FROM users_to_obfuscate u
WHERE u.id = users.id