Learning Postgres

Learning how to configure, use and backup Postgres databases.

Learning Postgres

At the moment this is less an article and more of a dumping zone for tidbits I would like to include in an article… And since I published it from the start of my website, I’ll leave it here and let it grow.

Note: Any name or variable that need to be provided is wrapped in curly brackets, example: {user_name}

psql

  • \x show expanded rows
  • \c {database_name} connect to database named database_name
  • \l list databases
  • \dt list tables
  • \du list users and access privileges
  • \dp list sequences and access privileges
  • \dn+ list schemas and access privileges
  • \s client command history

SQL Hints

  • Use single quotes for strings WHERE "callSign" = 'hooky'
  • Use double quotes around column names "User"."callSign"
-- Delete complete data from an existing tableTRUNCATE TABLE {table_name};
-- Update Roles to Racer and Super Adminupdate "public"."User" set roles = 65664 where "User"."callSign" = 'hooky';

Users, Roles, and Privileges

Create new user

CREATE USER {user_name} WITH PASSWORD {password};

Grant Privileges to Database

References

“Granting access to all tables for a user”
“ERROR: permission denied for sequence…”

Reminder: Switch to the database on which you want to grant privileges.

Example
-- Grant connect to the databaseGRANT CONNECT ON DATABASE {database_name} TO {user_name};-- Grant to a schemaGRANT USAGE ON SCHEMA {schema_name} TO {user_name};-- Grant all tablesGRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA {schema_name} TO {user_name};-- !Important-- Grant permission to sequencesGRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO {user_name};-- !Important-- Grant permission on future sequencesALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO {user_name};

Login in to psql as a different user to a specific database.

psql: FATAL: database ”” does not exist

psql -U {user_name} {database_name}

List Privileges on a Database

References

List the database privileges using psql

SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grantsWHERE NOT (grantee='postgres' OR grantee='PUBLIC');

Backup

pg_dump dbname > infile

Restoring

Databases are not automatically created when restoring from a dump. Before restoring with psql create the new database from template0. Also all users who own own or have permissions on objects in the dumped database must already exist before restoring. Otherwise it will not be restored with the same ownerships or permissions.

createdb -T template0 dbnamepsql dbname < infile

References