💾 Archived View for sdf.org › beni › tech › postgres.gmi captured on 2024-07-08 at 23:41:25. Gemini links have been rewritten to link to archived content

-=-=-=-=-=-=-

PostgreSQL Notes

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;