0

I would like to setup PostgreSQL in such a way that dropping and recreating the entire database (~100 tables/views + a few rows per table) is as fast as possible. This is for a development environment where data integrity is not important. This database reset will happen very often.

What are the configuration options that might help with this (or psql meta commands that can help speed this process up)

EDIT: Imagine i have a single sql file (that i constantly edit and save) that creates everything needed in an empty database.

BEGIN;
CREATE TABLE ...
INSERT INTO ...
CREATE TABLE ...
INSERT INTO ...
COMMIT;

I need it to run as fast as possible, right now it takes a few seconds even for about 10 tables, i would like it to be under 1 sec

11
  • 1
    The fastest way to re-create the database is to create it from a template create database dev template dev_base
    – user1822
    Jan 11, 2017 at 19:47
  • thanks, i don't think it fits my need (probably asked the question wrong, i'll add an update) Jan 11, 2017 at 23:55
  • Everyone would like their database to run faster. There isn't a magical option though. There is no SET run_speed_slow = false; that will make things magically speed up everywhere. You have to tell us what is slow. My answer gets you the fastest recreation time possible. What is not satisfactory about that? What do you want? Jan 12, 2017 at 0:35
  • This is what i want :) stackoverflow.com/questions/12206600/… and stackoverflow.com/questions/9407442/… and it looks like there are magic settings :). I'll test the solutions in those threads and reply back here Jan 12, 2017 at 0:44
  • These settings are probably the ones i am looking for postgresql.org/docs/current/static/non-durability.html but i have not tested them yet. I'll get back with results Jan 12, 2017 at 0:49

2 Answers 2

1

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,

  1. 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;
    
  2. Make this into a TABLESPACE

    CREATE TABLESPACE dbspace LOCATION '/data/dbs';
    
  3. 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.
2
  • Long and detailed answer (thank you) but that is not my question. Integrity is not important in the sense that i don't care if the data gets corrupted (because it was not flushed to the disk) when the power to my computer goes down. I am not interested in backing up my data at all. Imagine i have a single sql script/file that has everything (table definition and data), i need it to run as fast as possible when executed against an empty database Jan 11, 2017 at 23:49
  • I am sorry if my question was not clear enough and made you waste your time. Jan 12, 2017 at 0:54
1

Turns out i was looking for these settings https://www.postgresql.org/docs/current/static/non-durability.html These posts also helped https://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/

With settings turned off (what i need)

fsync = off
synchronous_commit = off
full_page_writes = off

$ time reset_db.sh 

real    0m0.725s
user    0m0.068s
sys 0m0.101s

With settings turned on (how it is by default)

fsync = on
synchronous_commit = on
full_page_writes = on

$ time reset_db.sh 

real    0m3.280s
user    0m0.069s
sys 0m0.102s

PS: thanks to everyone trying to help, sorry if the question was not clear enough.

3
  • I want to see the dump. Jan 12, 2017 at 7:50
  • The dump (sql file with DDL statements) is not important, this (my script/method) needs to work for anyone trying to iteratively work on the schema of his database, just by editing DDL statements in a plain sql file. The time or course will vary for each schema but the important part is that for this particular test case there was a big speedup with those settings which i think will also work for other schemas Jan 12, 2017 at 8:43
  • It's important because we can't verify what you're claiming. Jan 12, 2017 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.