Usability Challenges

## Core server management and configuration - Too much tuning - Memory management: too complex, there are few useful guidelines, most things could be automated - Vacuum: should be automatic -- yay, autovacuum - Background writer configuration: Who needs that? - Write-ahead log configuration: too complicated, should be automatic - Free-space map: The server knows full well how much FSM it needs; see also memory management. - Managability is lacking - User accounts: still no good way to manage pg_hba.conf from SQL - Statistics: too much data but most people don't know what to make of it - Configuration files: too long, too many options that most people don't need - Plugins: Using external modules is complicated, sometimes risky, hard to manage. - Logging: Logging configurability is great but the default configuration is less than useful for new users. - Tracing: Everything notwithstanding, it is still really hard at times to know what is happening, such as in nested PL/pgSQL calls, in cascaded foreign key actions, and other nested and cascaded contexts.* Clients: … No out-of-band monitoring is supported. If pg_ctl launched the postmaster but the postmaster can't start properly functioning backends, the only diagnostics are free-form text logs. This stinks for people trying to manage and automate PostgreSQL installs. An out-of-band monitoring tool is needed that can report things like the port(s) Pg is listening on, any errors produced when trying to start backends, memory status, running queries (w/o having to start a new backend just to query pg_stat_activity), lock status, etc. … ## Backups, `pg_dump`, `pg_dumpall` and `pg_restore` - The default encodings/locales selected on Windows and Linux (UTF8) systems are incompatible with each other, so running `pg_dump -Fc -f dbname.backup dbname`on Linux then `pg_restore -C --dbname postgres dbname.backup`on Windows (or vice versa) … `template0`can't be connected to, there's no DB they can always connect to by default. This leads to weird command lines like `pg_restore --create --dbname postgres mydb.backup`to *restore to a newly created database, probably but not necessarily called*. If the user omits `mydb`, not to the … `-Fc`mode. This means that *by default PostgreSQL database dumps cannot be restored correctly unless the user dumps additional information separately!*. `pg_dump`should include global objects like roles that are referred to by the database being dumped, so that backups are complete and correct by default. `pg_dumpall`doesn't support the custom format. You can't make an archive containing all databases on a cluster, or have it spit out one dump file per database plus a globals file. This must be done manually using scripting, and that's rather less than user friendly. Backups need to be easy to get right by default! … ## PgAdmin-III (First point of contact for most newbies) - PgAdmin-III usability may be somewhat lacking - Using the "Restore" dialog with PgAdmin-III and pointing it a .sql dump produces an unhelpful error message. It should offer to run the SQL dump against the target database, at least when faced with a … *have to be edited by hand before they can be restored*. - PgAdmin-III uses the unhelpful `.backup`suffix for backups it creates with `pg_dump -Fc`behind the scenes. Backup of *what?*There's nothing in `pg_restore`that says files should have a .backup extension, nor does it encourage them to be created as such, so users who want to restore a backup created from the command line via PgAdmin-III often have to rename the file or change the filter before they can even see it in the file list to restore. … `pg_restore`'s `-C`option. That's really counter-intuitive; you should just be able to select the server you want to restore to, or use the restore item in the menu and be prompted for the target server. - You don't get a choice of the database name to use for the newly created database with PgAdmin-III's Restore, Create Database option, it silently uses the db name in the backup file (and doesn't give you any indication of what it is). This is a … ## Replication - Built-in replication can't replicate only some databases of a cluster; you have to replicate both my-critically-important-10MB-database and my-totally-unimportant-50GB-database with the same settings, same priority, etc. This is a usability challenge because it means people have to create and manage multiple clusters to control replication groups, and multiple clusters are hard to manage and configure. See also Usability reviews