General commands
\l -- list databases
\c <db> -- connect to database
\dt -- list tables
\d <table> -- show columns of a table
\du -- list user roles
\h -- show help
ANALYZE and VACUUM
ANALYZE; -- Analyze all tables in current database
VACUUM; -- Vacuum all tables
-- Show relevant timestamps with regard to analyze and Vacuum
SELECT relname, last_vacuum, last_analyze, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;
-- Show dead tuples
SELECT relname, n_dead_tup FROM pg_stat_user_tables;
Activate autovacuum in postgresql.conf
track_counts = on
autovacuum = on
Change encoding of 'template1'
On a UTF-8 setup you might still require a LATIN1 database template because your supplier tells you so. Most likely you don't want this - but if you do, that's what you need to do:
UPDATE pg_database SET datistemplate=false WHERE datname='template1';
DROP DATABASE template1
CREATE DATABASE template1 WITH OWNER=postgres ENCODING = 'LATIN1' LC_CTYPE = 'POSIX' LC_COLLATE = 'POSIX' TEMPLATE = 'template0';
UPDATE pg_database SET datistemplate=true WHERE datname='template1';
Migrating a database to another encoding
Your only option is to dump the database and recreate it. Here is what I did when I had to migrate from UTF-8 to LATIN1. First dupm the database from a shell
psql_dump <db_name> > <db_name>.sql
Then move the database and recreate it with the correct settings
ALTER DATABASE <db_name> RENAME TO <db_name>_backup;
CREATE DATABASE <db_name> ENCODING = 'LATIN1' LC_CTYPE = 'POSIX' LC_COLLATE='POSIX' TEMPLATE template1;
Then restore the dump, setting the client to the old encoding
PGCLIENTENCODING=UTF8 psql -f <db_name>.sql <db_name>
If everything is successful drop the backup of the database
DROP DATABASE <db_name>_backup;