What configurations affect the speed of DDL statements in PostgreSQL
In what sense is database integrity not important? Even if you don't care about integrity, if the production database doesn't accept entries in table baz because of a foreign key constraint against table bar, do you want that constraint emulated in development? Or do you want to find out you forgot about it when you move to production? Personally, I would not sacrifice integrity in development.

Official toolkit, pg_dump, pg_restore

I would personally use pg_dump and pg_restore. They're configurable, user-friendly and very easy to use. That said, there are faster options but I have never had a need for them for my use cases. The official tools do not support binary dumps, but they also work between versions fairly well.  pg_restore -1 will wrap the whole thing in a transaction and disable WAL.

Raw Data with TABLESPACE

If you're willing to make raw backups you can get far greater speed. You can do this,


Create a a folder somewhere like /data/dbs chown it to be owned the user/group postgres

mkdir -p /data/dbs;
chown postgres:postgres /data/dbs;

Make this into a TABLESPACE

CREATE TABLESPACE dbspace LOCATION '/data/dbs';

Create a database there..

CREATE DATABASE foobar WITH TABLESPACE = dbspace;



Now you can connect to -d foobar. And do some stuff.

CREATE TABLE foo ( idfoo serial PRIMARY KEY, name text );
CREATE TABLE baz ( idbaz serial, idfoo int REFERENCES foo );
INSERT INTO foo (name) VALUES ('foo'), ('bar');
INSERT INTO baz (idfoo) SELECT idfoo FROM foo;


Now you can disconnect.. you can copy the data-directory to backup. this process can be scripted easily. For me,


the data directory is PG_9.5_201510051 for you it may different.
the command to stop PostgreSQL runs though /usr/sbin/service (I use Ubuntu)


So for me, it looks like this.

sudo service postgresql stop
sudo cp -pR PG_9.5_201510051/ backup
sudo service postgresql start


Reconnect.

DROP TABLE foo CASCADE;


Then when disconnect and again stop the service we can restore the backup (also easily scripted),

service postgresql stop;
cp -pR backup/ PG_9.5_201510051/


Upon reconnecting, foo is magically back and restored. You can't get faster than this...

Not fast enough?

If this isn't fast enough. In the event your database has TB of data or something crazy.


You can use rsync to only synchronize files changed.
You can likely use ZFS snapshots or BTRFS snapshots on a block device mounted to the data directory. YMMV.