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

Revisions

  1. mrstif revised this gist Feb 6, 2018. 1 changed file with 11 additions and 0 deletions.
    11 changes: 11 additions & 0 deletions list_users.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    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;
  2. mrstif revised this gist Feb 6, 2018. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions createuser.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,3 @@
    CREATE USER $USERNAME PASSWORD $PASSWORD;
    GRANT USAGE ON SCHEMA $SCHEMA TO $USERNAME;
    GRANT SELECT ON $TABLENAME TO $USERNAME;
  3. mrstif revised this gist May 15, 2017. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions tips.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,2 @@
    -- Check database connections
    SELECT application_name, client_addr from pg_stat_activity;
  4. mrstif revised this gist May 3, 2017. No changes.
  5. mrstif revised this gist May 3, 2017. 2 changed files with 4 additions and 1 deletion.
    2 changes: 1 addition & 1 deletion fast pg_restore settings → fast_pg_restore_settings.md
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@ http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html

    ```
    shared_buffers = 1/2 of what you'd usually set
    maintenance_work_mem = 1GB-2GB
    maintenance_work_mem = 1GB-2GB -- limit should be RAM/(2*concurrency)
    fsync = off
    synchronous_commit = off
    wal_level = minimal
    3 changes: 3 additions & 0 deletions tasks.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,3 @@
    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>
  6. mrstif created this gist May 3, 2017.
    36 changes: 36 additions & 0 deletions fast pg_restore settings
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,36 @@
    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
    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
    ```