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:
The smalldb would then contain a subset of largedb's data.
createdb smalldb
pg_sample largedb | psql smalldb
Here are the command-line options (many of which mirror pg_dump):
See also: pg_sample Github source repository
-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.
