Your responses to this survey are greatly appreciated and will assist me with my presentation at PGCon. Please send your responses directly to me at dan@coffeecode.net.

I’ll compile the results to redistribute to the community after; perhaps it will kick off an interesting mailing list thread about building a set of Evergreen-specific best practices or recommendations around PostgreSQL…

Identification

  • What’s your name?

  • What’s your Evergreen site?

  • What version of Evergreen are you running?

  • What Linux distribution & version are you running?

  • Where did you get PostgreSQL from:

    • Distribution packages (including backports)

    • Packages from PostgreSQL project

    • Compiled from source

Database stats

PostgreSQL version
SELECT version();
Database size
-- adjust 'evergreen' with your database name
SELECT pg_database_size('evergreen');
Interesting user table statistics
SELECT * FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20;
How many bib records?
SELECT COUNT(*) FROM biblio.record_entry;
How many items?
SELECT COUNT(*) FROM asset.copy;
How many circ transactions total?
SELECT COUNT(*) FROM (
    SELECT id FROM action.circulation
    UNION
    SELECT id FROM action.aged_circulation
) AS x;

Database server specs

  • Number of CPUs / cores?

  • Amount of RAM?

  • Disk RAID type (5, 10, etc)

  • Any particular disk partitioning strategy? (Separate disks for database logs vs. data, for example?)

Database configuration

  • How do you tune your database?

    • Use defaults

    • Manually, based on documentation

    • pg_tune

    • pg_tune + manual tweaks

  • Output of the following commands: 

SHOW shared_buffers;
SHOW effective_cache_size ;
SHOW max_connections;
SHOW maintenance_work_mem;
SHOW work_mem;
SHOW default_statistics_target;
SHOW checkpoint_segments;
SHOW wal_buffers;

  • Any other config parameters that you change that are important to you?

  • Backup strategy - do you have one?

    • Filesystem-level (snapshots, incrementals, etc)

    • Regular pg_dump

    • WAL archiving / shipping

  • Replication? If so, what solution: Slony, native streaming replication, other?

    • How many replicas?

    • Running reports / directing read-only queries to replicas?

  • Connection pooling? If so, what solution? pgpool, pgBouncer, other?

    • How many logical connections vs. physical connections?

  • Database upgrades:

    • When do you upgrade the version of PostgreSQL that you’re running (e.g. from 8.4 to 9.0):

      • Same time as the rest of your Evergreen upgrade

      • When you upgrade your underlying distro

      • As soon as a new version of PostgreSQL comes out?

    • How do you upgrade?

      • pg_dump and pg_restore

      • pg_upgradecluster (Debian / Ubuntu tool)

      • pg_upgrade (in-place upgrade tool)

PostgreSQL love and hate

  • What do you hate about running PostgreSQL?

  • What do you love about running PostgreSQL?