### 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 ```sql 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 <> ''; ``` #### 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 /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 ```sql -- 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) ```