Here are the steps needed to backup and restore an entire xTuple database using pg_dumpall:
-
Create the backup file:
pg_dumpall -c -U postgres --quote-all-identifiers --disable-triggers -f backup.sql
-
Edit the backup file, and insert the following script after every "\connect" line
-
-- xtuple/lib/orm/source/create_xt_schema.sql
do $$
declare
count integer;
query text;
begin
/* Only create the schema if it hasn't been created already */
perform *
from information_schema.schemata
where schema_name = 'xt';
get diagnostics count = row_count;
if (count > 0) then
return;
end if;
query = 'create schema xt;';
execute query;
query = 'grant all on schema xt to group xtrole;';
execute query;
end;
$$ language 'plpgsql';
-- xtuple/lib/orm/source/create_plv8.sql
-- dummy function avoids forward reference bug with some plv8 versions
CREATE OR REPLACE FUNCTION xt.js_init(debug BOOLEAN DEFAULT false, initialize BOOLEAN DEFAULT false)
RETURNS VOID AS $$ BEGIN RETURN; END; $$ LANGUAGE plpgsql;
CREATE EXTENSION IF NOT EXISTS plv8;
-
-
Restore the backup file (ostensibly on a BC/DR hot-spare, though you could also use this when upgrading postgresql versions):
psql -U postgres -f backup.sql
List of scripts needed:
- Export script:
#!/bin/bash
BF=/full/path/to/your_backup_file.sql
/full/path/to/your/postgresql/bin/pg_dumpall -c -U postgres --quote-all-identifiers --disable-triggers -f $BF - Import script:
#!/bin/bash
BF=/full/path/to/your_backup_file.sql
PLV8=/full/path/to/gils_script.sql
sed -e '/\\connect /r $PLV8' $BF > /tmp/$BF
chown postgres /tmp/$BF
rm $BF
cd /opt/local
/full/path/to/your/postgresql/bin/psql -U postgres -f /tmp/$BF
rm /tmp/$BF - "Create XT schema" script:
do $$
declare
count integer;
query text;
begin
perform *
from information_schema.schemata
where schema_name = 'xt';
get diagnostics count = row_count;
if (count > 0) then
return;
end if;
query = 'create schema xt;';
execute query;
query = 'grant all on schema xt to group xtrole;';
execute query;
end;
$$ language 'plpgsql';
CREATE OR REPLACE FUNCTION xt.js_init(debug BOOLEAN DEFAULT false, initialize BOOLEAN DEFAULT false)
RETURNS VOID AS $$ BEGIN RETURN; END; $$ LANGUAGE plpgsql;
CREATE EXTENSION IF NOT EXISTS plv8;