# RDS PostgreSQL Extensions Get Started ## RDS PostgreSQL Extensions Get Started ## 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;, ``` ## 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に対応しているので、こちらのバージョンをインストール (参照: ) ``` $ 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のスクリプト。 バージョンはこちらで確認 ``` $ 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: --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hayashier.gitbook.io/article/rdbms/rds-postgresql-extensions-get-started.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.