Review PostgreSQL Administration Cookbook

March 22, 2018 by dc

Some reviews about PostgreSQL Administration Cookbook

default databases

There are three databases: template0, template1, postgres * template0 and template1 are template when you create new databases * template1 can be changed to allow you to create a localized template for any new databases that you create * template0 database exists so that when you alter template1, you still have a pristine copy on which to fall back on * You can drop the database named postgres. But don’t, okay

default schema: public ANSI schema: information_schema PG schema: pg_catalog

information_schema

There is a second set of catalog objects named the Information Schema, which is the SQL standard way of accessing information in a relational database.

Revoke

The REVOKE command revokes previously granted privileges from one or more roles. The key word PUBLIC refers to the implicitly defined group of all roles.

REVOKE ALL ON SCHEMA finance FROM PUBLIC

Row level security

It’s possible to grant select/insert/update/delete privilege on some rows after enableing ROW LEVEL SECURITY

Backup & Recovery

  • Logical backup: pg_dump
  • Physical backup: copy all data directory

© 2018 | Follow on Twitter | Hucore theme & Hugo