Skip to content

Instantly share code, notes, and snippets.

@mrstif

mrstif/createuser.sql

Last active February 6, 2018 10:21
Show Gist options
  • Save mrstif/51af94b9a66f1c7124ede8dfa7df3b1b to your computer and use it in GitHub Desktop.
Save mrstif/51af94b9a66f1c7124ede8dfa7df3b1b to your computer and use it in GitHub Desktop.
Download ZIP
Postgres
Raw
createuser.sql
CREATE USER $USERNAME PASSWORD $PASSWORD;
GRANT USAGE ON SCHEMA $SCHEMA TO $USERNAME;
GRANT SELECT ON $TABLENAME TO $USERNAME;
Raw
fast_pg_restore_settings.md

http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html

  • number of jobs (-j) should be set to two less than the number of cores you have, up to a limit of 8.
shared_buffers = 1/2 of what you'd usually set 
maintenance_work_mem = 1GB-2GB -- limit should be RAM/(2*concurrency)
fsync = off
synchronous_commit = off
wal_level = minimal
full_page_writes = off
wal_buffers = 64MB
checkpoint_segments = 256 or higher -- DEPRECATED in PG 9.5 (use min_wal_size and max_wal_size instead)
max_wal_size = (3 * checkpoint_segments) * 16MB -- >= 9.5
max_wal_senders = 0
wal_keep_segments = 0
archive_mode = off
autovacuum = off
all activity logging settings disabled

Example:

shared_buffers = 64
maintenance_work_mem = 1GB
fsync = off
synchronous_commit = off
wal_level = minimal
full_page_writes = off
wal_buffers = 64MB
max_wal_size = 1024
max_wal_senders = 0
wal_keep_segments = 0
archive_mode = off
autovacuum = off
Raw
list_users.sql
SELECT u.usename AS "User name",
u.usesysid AS "User ID",
CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
ELSE CAST('' AS pg_catalog.text)
END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
Raw
tasks.sh
pg_restore --verbose --clean --no-acl --no-owner -Fd -j 2 -w -h <HOST> -p <PORT> -U <USER> -d <DATABASE> <FILENAME>
PGPASSWORD=<PASSWORD> pg_dump --verbose --no-owner -n public -Fd -j 2 -w -h <HOST> -p <PORT> -U <USER> <DATABASE> -f <FILENAME>
Raw
tips.sql
-- Check database connections
SELECT application_name, client_addr from pg_stat_activity;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment