RDS PostgreSQL に拡張機能導入 (pgaudit, pg_repack, PostGIS, pgBadger, pg_config)
pgaudit 有効化
$ psql -U hayashier -h postgres-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com -d test
Password for user hayashier:
psql (9.6.8, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type help for help.
test=> CREATE ROLE rds_pgaudit;
CREATE ROLE
パラメータグループで、pgaudit.roleの値をrds_pgaudit,shared_preload_librariesの値をpgauditに変更し再起動後、変更が適用される。
$ aws rds modify-db-parameter-group --db-parameter-group-name psql9-6-parameter-groups --parameters ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot --parameters ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot --region us-west-2;
{
DBParameterGroupName: psql9-6-parameter-groups
}
$ aws rds reboot-db-instance --db-instance-identifier postgres-test --region us-west-2
{
DBInstance: {
PubliclyAccessible: false,
MasterUsername: hayashier,
MonitoringInterval: 60,
LicenseModel: postgresql-license,
:
:
shared_preload_librariesにpgaudit, pgaudit.roleにrds_pgauditが設定されていることを確認。
test=> SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
test=> SHOW pgaudit.role;
pgaudit.role
--------------
rds_pgaudit
(1 row)
監査ログが出力されることのテスト
test=> CREATE TABLE t1 (id int);
CREATE TABLE
test=> GRANT SELECT ON t1 TO rds_pgaudit;
GRANT
test=> SELECT * FROM t1;
id
----
(0 rows)
error/postgresql.log.20YY-MM-dd-hh に監査ログが出力されていることを確認。
2018-07-29 10:49:29 UTC:172.31.19.138(54768):hayashier@test:[22378]:LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.t1,SELECT * FROM t1;,<not logged>
pg_repack 導入
pg_repackの導入
test=> CREATE EXTENSION pg_repack;
CREATE EXTENSION
test=> ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES
test=> ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
ALTER DEFAULT PRIVILEGES
pg_repackのクライアントをインストール。RDS PostgreSQL 9.6.6を使用し、こちらはpg_repack 1.4.2に対応しているので、こちらのバージョンをインストール (参照: https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.96x)
$ sudo yum install postgresql96-static -y
$ wget http://api.pgxn.org/dist/pg_repack/1.4.2/pg_repack-1.4.2.zip pg_repack-1.4.2.zip
$ unzip pg_repack-1.4.2.zip
$ cd pg_repack-1.4.2
$ make
$ sudo make install
pg_repackがインストールされたことの確認
$ ~/pg_repack-1.4.2/bin/pg_repack --version
pg_repack 1.4.2
test=> select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
pg_stat_statements | 10 | 2200 | t | 1.4 | |
pgaudit | 10 | 2200 | t | 1.0 | |
pg_repack | 10 | 2200 | f | 1.4.2 | |
(4 rows)
pg_repackを実行
$ ~/pg_repack-1.4.2/bin/pg_repack --no-superuser-check --table=testtbl --dbname=test --host=postgres-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com --port=5432 --username=hayashier --password --elevel=DEBUG
DEBUG: No workers to disconnect.
Password:
Password:
INFO: repacking table "testtbl"
DEBUG: ---- repack_one_table ----
DEBUG: target_name : testtbl
:
:
PostGIS 導入
ユーザー作成と権限の付与
$ psql -U hayashier -h postgres-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com -d test
Password for user hayashier:
psql (9.6.8, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
test=> CREATE USER u1 WITH PASSWORD 'password';
CREATE ROLE
test=> ALTER ROLE u1 CREATEDB;
ALTER ROLE
test=> GRANT RDS_SUPERUSER TO u1;
GRANT ROLE
test=> CREATE DATABASE u1db1;
CREATE DATABASE
test=> \c u1db1
psql (9.6.8, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "u1db1" as user "hayashier".
u1db1=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------------------------
hayashier | Create role, Create DB +| {rds_superuser}
| Password valid until infinity |
rds_replication | Cannot login | {}
rds_superuser | Cannot login | {rds_replication,pg_signal_backend}
rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| Password valid until infinity |
rdsrepladmin | No inheritance, Replication +| {}
| Password valid until infinity |
u1 | Create DB | {rds_superuser}
PostGISのインストール
u1db1=> CREATE EXTENSION postgis;
CREATE EXTENSION
u1db1=> CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
u1db1=> CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
u1db1=> CREATE EXTENSION postgis_topology;
CREATE EXTENSION
u1db1=> ALTER SCHEMA tiger owner TO rds_superuser;
ALTER SCHEMA
u1db1=> ALTER SCHEMA tiger_data owner TO rds_superuser;
ALTER SCHEMA
u1db1=> ALTER SCHEMA topology owner TO rds_superuser;
ALTER SCHEMA
u1db1=> CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
u1db1=> SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser;')
u1db1-> FROM (
u1db1(> SELECT nspname, relname
u1db1(> FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
u1db1(> WHERE nspname in ('tiger','topology') AND
u1db1(> relkind IN ('r','S','v') ORDER BY relkind = 'S')
u1db1-> s;
exec
--------------------------------------------------------------------
ALTER TABLE tiger.loader_variables OWNER TO rds_superuser;
ALTER TABLE tiger.loader_lookuptables OWNER TO rds_superuser;
ALTER TABLE tiger.street_type_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.tract OWNER TO rds_superuser;
ALTER TABLE tiger.place_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.tabblock OWNER TO rds_superuser;
ALTER TABLE tiger.county_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.bg OWNER TO rds_superuser;
ALTER TABLE tiger.countysub_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_gaz OWNER TO rds_superuser;
ALTER TABLE tiger.featnames OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_lex OWNER TO rds_superuser;
ALTER TABLE tiger.zip_lookup_all OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_rules OWNER TO rds_superuser;
ALTER TABLE tiger.zip_lookup_base OWNER TO rds_superuser;
ALTER TABLE topology.topology OWNER TO rds_superuser;
ALTER TABLE topology.layer OWNER TO rds_superuser;
ALTER TABLE tiger.zip_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.addr OWNER TO rds_superuser;
ALTER TABLE tiger.county OWNER TO rds_superuser;
ALTER TABLE tiger.faces OWNER TO rds_superuser;
ALTER TABLE tiger.state OWNER TO rds_superuser;
ALTER TABLE tiger.geocode_settings OWNER TO rds_superuser;
ALTER TABLE tiger.place OWNER TO rds_superuser;
ALTER TABLE tiger.zip_state OWNER TO rds_superuser;
ALTER TABLE tiger.zip_state_loc OWNER TO rds_superuser;
ALTER TABLE tiger.geocode_settings_default OWNER TO rds_superuser;
ALTER TABLE tiger.cousub OWNER TO rds_superuser;
ALTER TABLE tiger.direction_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.edges OWNER TO rds_superuser;
ALTER TABLE tiger.secondary_unit_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.addrfeat OWNER TO rds_superuser;
ALTER TABLE tiger.state_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.zcta5 OWNER TO rds_superuser;
ALTER TABLE tiger.loader_platform OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_lex_id_seq OWNER TO rds_superuser;
ALTER TABLE tiger.featnames_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.addr_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.zcta5_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.county_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.state_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.place_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.cousub_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.edges_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.addrfeat_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.faces_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.tract_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.tabblock_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.bg_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_gaz_id_seq OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_rules_id_seq OWNER TO rds_superuser;
ALTER TABLE topology.topology_id_seq OWNER TO rds_superuser;
(52 rows)
u1db1=> SET search_path=public,tiger;
SET
u1db1=> \dn
List of schemas
Name | Owner
------------+---------------
public | hayashier
tiger | rds_superuser
tiger_data | rds_superuser
topology | rds_superuser
(4 rows)
インストールされたことの確認
u1db1=> select postgis_version();
postgis_version
---------------------------------------
2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
動作確認
u1db1=> SELECT na.address, na.streetname, na.streettypeabbrev, na.zip FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip
---------+------------+------------------+-------
1 | Devonshire | Pl | 02109
(1 row)
u1db1=> SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
----------------
1
(1 row)
u1db1=> SELECT box2d(ST_GeomFromText('LINESTRING(1 2,3 4, 5 6)'));
box2d
--------------
BOX(1 2,5 6)
(1 row)
pgBadger
ログをパースした結果が分かりやすくビジュアライズされる。 ツールをダウンロール。ツールは、pgbadgerというPerlのスクリプト。 バージョンはこちらで確認 https://github.com/dalibo/pgbadger/releases
$ wget https://github.com/dalibo/pgbadger/archive/v9.2.zip
$ unzip v9.2.zip
$ cd pgbadger-9.2/
$ chmod +x pgbadger
pgBadgerの実行。実行後、out.htmlという内容がBase64されたHTMLファイルが生成される。
$ ./pgbadger -p '%t:%r:%u@%d:[%p]:' postgresql.log.2018-07-29-10
[========================>] Parsed 7518 bytes of 7518 (100.00%), queries: 0, events: 8
LOG: Ok, generating html report...
pg_config の内容確認
test=> select * from pg_config();
name | setting
-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
BINDIR | /rdsdbbin/postgres-9.6.6.R1/bin
DOCDIR | /rdsdbbin/postgres-9.6.6.R1/share/doc
HTMLDIR | /rdsdbbin/postgres-9.6.6.R1/share/doc
INCLUDEDIR | /rdsdbbin/postgres-9.6.6.R1/include
PKGINCLUDEDIR | /rdsdbbin/postgres-9.6.6.R1/include
INCLUDEDIR-SERVER | /rdsdbbin/postgres-9.6.6.R1/include/server
LIBDIR | /rdsdbbin/postgres-9.6.6.R1/lib
PKGLIBDIR | /rdsdbbin/postgres-9.6.6.R1/lib
LOCALEDIR | /rdsdbbin/postgres-9.6.6.R1/share/locale
MANDIR | /rdsdbbin/postgres-9.6.6.R1/share/man
SHAREDIR | /rdsdbbin/postgres-9.6.6.R1/share
SYSCONFDIR | /rdsdbbin/postgres-9.6.6.R1/etc
PGXS | /rdsdbbin/postgres-9.6.6.R1/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE | '--prefix=/rdsdbbin/postgres-9.6.6.R1' '--with-openssl' '--with-perl' '--with-tcl' '--with-ossp-uuid' '--with-libxml' '--with-libraries=/rdsdbbin/postgres-9.6.6.R1/lib' '--with-includes=/rdsd
bbin/postgres-9.6.6.R1/include' '--enable-debug'
CC | gcc
CPPFLAGS | -D_GNU_SOURCE -I/usr/include/libxml2 -I/rdsdbbin/postgres-9.6.6.R1/include
CFLAGS | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -
g -O2
CFLAGS_SL | -fPIC
LDFLAGS | -L../../src/common -L/rdsdbbin/postgres-9.6.6.R1/lib -Wl,--as-needed -Wl,-rpath,'/rdsdbbin/postgres-9.6.6.R1/lib',--enable-new-dtags
LDFLAGS_EX |
LDFLAGS_SL |
LIBS | -lpgcommon -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION | PostgreSQL 9.6.6
(23 rows)
REFERENCE
- PostgreSQL の一般的な DBA タスク - Amazon Relational Database Service
- URL: https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html
