1. Knowledge Base
  2. Admin
  3. Database Administration

How do I backup and restore an xTuple database?

Here are the steps needed to backup and restore an entire xTuple database using pg_dumpall:

  1. Create the backup file:

    pg_dumpall -c -U postgres --quote-all-identifiers --disable-triggers -f backup.sql
  2. 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;
  3. 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;