Skip to content

Instantly share code, notes, and snippets.

@junqueira

junqueira/postgres.md

Forked from simondobner/postgres.md
Created December 28, 2017 23:06
Show Gist options
  • Save junqueira/dfa87f61d1ee29e8720d3565c138a4fa to your computer and use it in GitHub Desktop.
Save junqueira/dfa87f61d1ee29e8720d3565c138a4fa to your computer and use it in GitHub Desktop.
Download ZIP
Postgres notes
Raw
postgres.md

Database commands in psql

Connect to psql as a non postgres user $ psql -d testdbname - when the os user exists in the db and trusted authentication is enabled.

# create database db2; - create a database
# drop database db2; - drop a database
# \c[onnect] db2 - connect to a database
# \l[ist] - list all databases
# \l+ - list all databases with extra details, including size

Starting and Stopping the Postgres service

On Modern Redhat and Arch linux

# systemctl list-unit-files - check the name of the service. # systemctl status postgresql.service - check status
# systemctl start postgresql.service - stop service
# systemctl stop postgresql.service - start service

On Redhat from the olden days

chkconfig --list - confirm the name of the service # service postgresql-9.0 stop
# service postgresql-9.0 start

Configuration

Find the config files here....

Reload the configuration

SELECT pg_reload_conf(); -- check the log, if theres problems it won't reload, it just gets logged.

Database Metadata

Sizings

select pg_database_size('databaseName'); - size of a database

How big are my schemas

Note that this includes all indexes, toast and other space consumers on tables

SELECT schemaname, (SUM(pg_total_relation_size(quote_ident(schemaname) ||
                                               '.' || quote_ident(tablename)))::BIGINT)/1024/1024 
FROM pg_tables
group by schemaname
order by 1;

Clone a database

This method lets you import into the same instance with a different name ( and of course, into other instances)
Export the full database as a custom dump ( -F = c )
pg_dump -h hostname -p 5432 -U usename -F c -b -v -f prd_export.backup source_database

Create the new database, and add in any required extensions, set owner

geoscape_qa_prd=> create database geoscape_qa_tst
geoscape_qa_tst=> CREATE EXTENSION "uuid-ossp";
geoscape_qa_tst=>  create extension postgis;
geoscape_qa_tst=> CREATE EXTENSION tablefunc;
geoscape_qa_tst=> alter database geoscape_qa_tst owner to new_owner ;

And now you can import it with pg_restore

$ pg_restore  -h servername.czd33swenehpx.ap-southeast-2.rds.amazonaws.com  -U uname  -d new_db  prd_export.backup

if you're in RDS, you will need to ignore these errors (due to non-superuserness..)

pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension uuid-ossp
    Command was: COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';

Tell me about the postgres am I running

postgres=# select version(); -- version of postgres select inet_server_addr(); -- the servers IP address
select current_user -- who am I ?

Sessions and what's happening in the database

List currently active sessions

SELECT datname,usename, procpid,client_addr,waiting,query_start,current_query 
FROM pg_stat_activity
where current_query <> '<IDLE>';

List all sessions connected to a database

postgres=> SELECT pid FROM pg_stat_activity where datname = 'dbname';

Doing things to sessions

SELECT pg_terminate_backend(procpid) - kill one

Files and Directories

postgres=# show config_file -- Where is my postgres.conf
postgres=# show data_directory
The wal directory is pg_xlog under the data directory

Moving Data

Export a complete database

$ pg_dump geoscape >contributor_dg.dmp

To export specific tables

$ pg_dump databasename -t schema.buildings_ur01 -t schema.buildings_other_table >buildings.dump

and to import the same in a different db (make sure the schema exists)

psql -d targetdbname <buildings.dump

How to duplicate a schema in the same database

pg_dump -n geodesy geodesydb >/tmp/datadump - export the schema
edit the dumpfile and change the create schema and alter schema lines
from

CREATE SCHEMA geodesy;
ALTER SCHEMA geodesy  OWNER TO geodesy;

to

CREATE SCHEMA geodesy2;
ALTER SCHEMA geodesy2  OWNER TO geodesy;

check to be sure that there isn't already a geodesy2 schema
psql -U geodesy -f /tmp/datadump geodesydb - import the objects from the old schema into the new

Tables

alter table tablename to newTableName;
alter index indexname to newIndexName;

Logging

https://www.postgresql.org/docs/current/static/runtime-config-logging.html

Quick and dirty logging configuration

these go into postgresql.conf

log_statement = 'ddl'
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'                # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,

Where is the config file

This is on nixos

[simon@ga:~]$ sudo  su -

[root@ga:~]# psql postgres
psql (9.4.6)
Type "help" for help.

postgres=# show config_file;
            config_file             
------------------------------------
 /var/db/postgresql/postgresql.conf

verify available extensions

SELECT name, default_version,installed_version 
FROM pg_available_extensions WHERE name LIKE 'postgis%' ;

or use \dx

geoscape=# \dx
                                     List of installed extensions
  Name   | Version |   Schema   |                             Description                          
   
---------+---------+------------+------------------------------------------------------------------
---
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 2.1.8   | public     | PostGIS geometry, geography, and raster spatial types and functio
ns

Find a file mapped to a table name

select t.relname, current_setting('data_directory')||'/'||pg_relation_filepath(t.oid)
from pg_class t
  join pg_namespace ns on ns.oid = t.relnamespace
where relkind = 'r'
and ns.nspname = 'public';  -- in this case its all tables in the public schema

restore a database from a dump into RDS

Remember, it matters if the user exists or not, but you can connect as a master user and the import will use it's privs to proceed - it just needs to know which schema to import the objects into.

pg_restore --verbose --clean --no-acl --no-owner -U sd_test_user -d sd_test custom_dump_3_schemas.dump
--clean
Clean (drop) database objects before recreating them
--no-acl
Prevent restoration of access privileges (grant/revoke commands).
--no-owner
Do not output commands to set ownership of objects to match the original database

User/Role management

create role simond with password 'password'; - make an account
alter user simond with superuser; - make a user a superuser
alter user simond with rds_superuser; - make a user a (almost) superuser in rds
alter user simond with login; - make a user able to login

Grant access to select any table in a database

ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO username; --give access to all future created tables
GRANT SELECT ON ALL TABLES in schema contributor_dg TO username; -- give access to tables in this schema, repeat for each schema in the database

Granting access to schema, tables in that schema, and future created tables example

-- enable a user to access, and create, objects in a schema
grant  usage on schema staging to wms_master ;
grant  create on schema staging to wms_master ;

-- enable a user to delete, select, insert, truncate all (currently existant) objects in a schema
grant delete on all tables in schema staging to wms_master;
grant select on all tables in schema staging to wms_master;
grant insert on all tables in schema staging to wms_master;
grant truncate on all tables in schema staging to wms_master;

-- makit so that new tables will auto inherit these privs
ALTER DEFAULT PRIVILEGES IN SCHEMA staging GRANT SELECT ON TABLES TO wms_master;
ALTER DEFAULT PRIVILEGES IN SCHEMA staging GRANT truncate ON TABLES TO wms_master;

-- set the default search path for a user
alter role uname set search_path to uname_schema ;

Find out about users and roles

select * from pg_roles -- list roles (and users) in an instance
select * from pg_auth_members -- list role membership, grantor, admin rights

-- what roles does a user have
 SELECT oid, rolname FROM pg_roles WHERE
   pg_has_role( 'uname', oid, 'member');

-- what users have a role (this sql shows all users)
SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)                                  
WHERE r.rolcanlogin
ORDER BY 1;

-- or, in psql
\du uname

Get the list of tables and priviliges a role (or user) has access to

SELECT grantee, table_schema, table_name, privilege_type 
FROM information_schema.role_table_grants 
WHERE grantee = 'uname';

Display permissions/grants on a table

product_quality_prd=>  \dp  schema.tablename 
                                                    Access privileges
   Schema    |            Name             | Type  |          Access privileges           | Column privileges | Polici
es 
-------------+-----------------------------+-------+--------------------------------------+-------------------+-------
---
 schema      | tablename                   | table | user1=r/owner                        +|                   | 
             |                             |       | user2=r/owner                        +|                   | 
             |                             |       | owner=arwdDxt/owner                  +|                   | 
             |                             |       | role1=r/owner                         |                   | 
(1 row)

Allow access to an archinux postgres server

obviously, use 'all' carefully.

sudo vi /var/lib/postgres/data/pg_hba.conf
and
host    all             all              all                     trust

update postgresql.conf to set listen_address to the hosts you want to listen out for

listen_addresses = '*'          # what IP address(es) to listen on;

and update hosts.allow

[simond@tbox ~]$ cat /etc/hosts.allow 
sshd: ALL
postgresql: ALL

Archiving, Backup and Restore

postgres=# select pg_switch_xlog() - switch the current wal (redo) log.

psql tips and tricks

set up defaults with a .psqlrc file

$ cat ~/.psqlrc 
\set PROMPT1 '%m: %n@%/%R%#%x '
\timing
\set HISTSIZE 10000

Repeat a statement many times

insert into x2(c1) select x.id || now() from generate_series(1,100) as x(id);

Migrate to RDS Tips and Tricks

This can be painful
Grab the user/role definitions via pg_dumpall
$ pg_dumpall -h hostname --roles-only -U dbuser > role_dumpall.sql
then user grep to grab the ones you are interested in ( or take em all )

BUT,
RDS doesn't play nicely with superuser type privs - it won't even accept NOSUPERUSER, so you will need to remove it, and NOREPLICATION from the create statement

From here you may be best doing the same thing with schemas --schema-only, and the follow with the data --data-only - at least this will help narrow down problems as they happen.

Update every nth record in a table

UPDATE schema.tablename
SET geometry = NULL
WHERE id IN (
  SELECT id
  FROM (
         SELECT
           *,
           row_number()
           OVER (
             ORDER BY id ASC) AS row
         FROM schema.tablename
       ) t
  WHERE t.row % 5000 = 0)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment