pg_sample: extract a sample dataset from a larger PostgreSQL database

pg_sample is a PostgreSQL utility for making smaller versions of large databases.

download pg_sample 0.01

When you have a relatively large database (tables with, say, millions or billions of rows), it can be difficult to generate smaller datasets to work with, especially if foreign keys are heavily used.

That's where this script comes in. It will create smaller instances of each table along with any additional rows needed to satisfy foreign key constraints (circular dependencies are supported).

The script's operation closely resembles that of pg_dump. For example, assuming we have a large database named largedb, a smaller version could be produced with:

  createdb smalldb
  pg_sample largedb | psql smalldb

The smalldb would then contain a subset of largedb's data.

Here are the command-line options (many of which mirror pg_dump):

    -a
    --data-only
        Output only the data, not the schema (data definitions).

    -E *encoding*
    --encoding=*encoding*
        Use the specified character set encoding. If not specified, uses the
        environment variable PGCLIENTENCODING, if defined; otherwise, uses
        the encoding of the database.

    -f *file*
    --file=*file*
        Send output to the specified file. If omitted, standard output is
        used.

    --force
        Drop the sample schema if it exists.

    --keep
        Don't delete the sample schema when the script finishes.

    --limit=*number*
        The maximum number of rows to initially copy from each table
        (defaults to 100). Note that sample tables may end up with
        significantly more rows in order to satisfy foreign key constraints.

    --random
        Randomize the rows initially selected from each table. May
        significantly increase the running time of the script.

    --schema=*name*
        The schema name to use for the sample database (defaults to
        _pg_sample).

    --trace
        Turn on Perl DBI tracing. See the DBI module documentation for
        details.

    --verbose
        Output status information to standard error.

    The following options control the database connection parameters.

    -h *host*
    --host=*host*
        The host name to connect to. Defaults to the PGHOST environment
        variable if not specified.

    -p *port*
    --port=*port*
        The database port to connect to. Defaults to the PGPORT environment
        variable, if set; otherwise, the default port is used.

    -U *username*
    --username=*username*
        User name to connect as.

    -W *password*
    -password=*password*
        Password to connect with.

See also: pg_sample Github source repository

Postgresql: Indexes on Foreign Keys

This query identifies foreign keys that are potentially missing indexes (Postgresql does not create indexes on foreign keys automatically).

/*
  Look for foreign key constraints that are missing indexes on the
  referencing table.

  Orders results by the size of the referencing table, largest first,
  on the assumption that, all else being equal, they are the most likely
  to benefit from the addition of indexes.

  This is only meant as a starting point, and isn't perfect.
  It's possible, for example, that it will report a missing index
  when in fact one is available. e.g., it won't realize that an index on
  (f1, f2) could be used with a fk on (f1). However, it will recognize
  that an index on (f1, f2) can be used with a fk on (f2, f1).

  Usage: psql -q dbname -f pg-find-missing-fk-indexes.sql
*/

CREATE FUNCTION pg_temp.sortarray(int2[]) returns int2[] as '
  SELECT ARRAY(
      SELECT $1[i]
        FROM generate_series(array_lower($1, 1), array_upper($1, 1)) i
    ORDER BY 1
  )
' language sql;

  SELECT conrelid::regclass
         ,conname
         ,reltuples::bigint
    FROM pg_constraint
         JOIN pg_class ON (conrelid = pg_class.oid)
   WHERE contype = 'f'
         AND NOT EXISTS (
           SELECT 1
             FROM pg_index
            WHERE indrelid = conrelid
                  AND pg_temp.sortarray(conkey) = pg_temp.sortarray(indkey)
         )
ORDER BY reltuples DESC
;

iconv: file too large

The iconv utility is used to convert file encodings. I'm using it to convert a Postgresql database from LATIN1 to UTF8.

However, the standard iconv program slurps the entire file into memory, which doesn't work for large data sets (such as database exports). You'll see errors like:

iconv: unable to allocate buffer for input: Cannot allocate memory
iconv: cannot open input file `database.txt': File too large

This script is just a wrapper that processes the input file in manageable chunks and writes it to standard output: iconv-chunks

Postgresql Performance Tips for Data Loads

• Turn off fsync in postgresql.conf. This can make a huge difference.


• Temporarily disable triggers.
  
  Postgresql 8.x supports an ALTER statement:
  
  ALTER TABLE foo DISABLE TRIGGER ALL;
ALTER TABLE foo ENABLE TRIGGER ALL;
  
  With Postgresql 7.x you need to modify the system tables directly:
  

  UPDATE pg_class
     SET reltriggers = 0
   WHERE relname = 'foo';

  To re-enable:
  

  UPDATE pg_class
     SET reltriggers = (
           SELECT count(*)
             FROM pg_trigger
            WHERE pg_class.oid = tgrelid
         )
   WHERE relname = 'foo';

• Postgresql docs on efficient data loads

Creating a Postgresql Development Database

Notes on how to restore a Pg database for a dev environment to make it simple to create additional copies or revert back to the production schema.

Drop and restore your db from a recent backup. Include a datestamp in the database name to keep track of the version you're restoring from. The specifics of this commands will vary depending on your permissions, backup method, etc.

$ dropdb mydevdb_20070106
$ createdb -U postgres -E LATIN1 mydevdb_20070106
$ pg_restore -U postgres -d mydevdb_20070106 ~/db-backup.2007-01-06

If you're restoring from production data, be sure to munge it at this point to remove all valuable/sensitive information, and delete the backup file so your dev environment isn't a target.

Now with this restored version of the database you can create any number of additional copies to actually work on. Leave the initial restore untouched so you can always revert back to it.

$ psql mydevdb_20070106
mydevdb_20070106=# CREATE DATABASE mydevdb WITH TEMPLATE mydevdb_20070106;
CREATE DATABASE
mydevdb_20070106=# CREATE DATABASE toms_devdb WITH TEMPLATE mydevdb_20070106;
CREATE DATABASE