Skip to content

Latest commit

 

History

History
146 lines (106 loc) · 10.5 KB

README.md

File metadata and controls

146 lines (106 loc) · 10.5 KB
Raw

aiven-extras

This extension is meant for use in enviroments where you want non-superusers to be able to use certain database features, such as creating logical replication connections using PostgreSQL® 10+'s logical replications SUBSCRIPTION/PUBLICATION concepts or configuring the auto_explain extension.

It allows you to use logical replication when installed originally by root and the user using it has the REPLICATION privilege.

Please note that when loading the auto_explain extension, it is loaded for the current session only.

Installation

To create the Aiven extras extension, run the following after connecting to the database you wish to enable it in:

$ CREATE EXTENSION aiven_extras;
CREATE EXTENSION

Usage

The functions included are:

Name Result data type Argument data types
auto_explain_load void
claim_public_schema_ownership void
dblink_record_execute SETOF record text, text
dblink_slot_create_or_drop void arg_connection_string text, arg_slot_name text, arg_action text
explain_statement SETOF json arg_query text
pg_alter_subscription_disable void arg_subscription_name text
pg_alter_subscription_enable void arg_subscription_name text
pg_alter_subscription_refresh_publication void arg_subscription_name text, arg_copy_data boolean DEFAULT true
pg_create_publication void arg_publication_name text, arg_publish text, VARIADIC arg_tables text[] DEFAULT ARRAY[]::text[]
pg_create_publication_for_all_tables void arg_publication_name text, arg_publish text
pg_create_subscription void arg_subscription_name text, arg_connection_string text, arg_publication_name text, arg_slot_name text, arg_slot_create boolean DEFAULT false, arg_copy_data boolean DEFAULT true
pg_drop_subscription void arg_subscription_name text
pg_list_all_subscriptions SETOF aiven_extras.aiven_pg_subscription
pg_stat_replication_list SETOF aiven_extras.aiven_pg_stat_replication
session_replication_role text arg_parameter text
set_auto_explain_log_analyze text arg_parameter text
set_auto_explain_log_buffers text arg_parameter text
set_auto_explain_log_format text arg_parameter text
set_auto_explain_log_min_duration text arg_parameter text
set_auto_explain_log_nested_statements text arg_parameter text
set_auto_explain_log_timing text arg_parameter text
set_auto_explain_log_verbose text arg_parameter text
set_pgaudit_parameter void arg_parameter text, arg_database text, arg_value text
set_pgaudit_role_parameter void arg_parameter text, arg_role text, arg_value text

Examples

Managing subscriptions:

-- Create a new subscription
SELECT * FROM aiven_extras.pg_create_subscription(
    'subscription',
    'dbname=defaultdb host=destination-demoprj.aivencloud.com port=26882 sslmode=require user=avnadmin password=secret',
    'pub1',
    'slot',
    TRUE,
    TRUE
);

-- List all subscriptions
SELECT * FROM aiven_extras.pg_list_all_subscriptions();

-- Drop a subscription
SELECT * FROM aiven_extras.pg_drop_subscription('subscription');

-- Create a publication `pub1` with `publish='INSERT,UPDATE'` for tables `foo` and `bar`
-- (Note: use fully qualified names)
SELECT * FROM aiven_extras.pg_create_publication('pub1', 'INSERT,UPDATE', 'public.foo', 'public.bar');

-- Create a publication `pub2` with `publish='INSERT'` for all tables
SELECT * FROM aiven_extras.pg_create_publication_for_all_tables('pub2', 'INSERT');

-- Disable a subscription
SELECT * FROM aiven_extras.pg_alter_subscription_disable('subscription');

-- Enable a subscription
SELECT * FROM aiven_extras.pg_alter_subscription_enable('subscription');

-- Refresh a subscribed publication with `copy_data` set to FALSE
SELECT * FROM aiven_extras.pg_alter_subscription_refresh_publication('subscription', FALSE);

Configuring auto-explain:

For details, refer to PostgreSQL's documentation, but note that arguments for the exposed functions are of type text. Also note that currently aiven_extras implements a subset of the available functions.

-- Load the extension for the current session
SELECT * FROM aiven_extras.auto_explain_load();

-- Set minimum duration to 2000 ms
SELECT * FROM aiven_extras.set_auto_explain_log_min_duration('2000');

-- Enable ANALYZE
SELECT * FROM aiven_extras.set_auto_explain_log_analyze('on');

License

Aiven extras Extension is licensed under the Apache License, Version 2.0. Full license text is available in the LICENSE file and at http://www.apache.org/licenses/LICENSE-2.0.txt

Credits

Aiven extras extension was created by Hannu Valtonen hannu.valtonen@aiven.io for Aiven Cloud Database and is now maintained by Aiven developers.

Aiven Cloud Database: https://aiven.io/

Recent contributors are listed on the GitHub project page, https://github.com/aiven/aiven-extras/graphs/contributors

Contact

Bug reports and patches are very welcome, please post them as GitHub issues and pull requests at https://github.com/aiven/aiven-extras . Any possible vulnerabilities or other serious issues should be reported directly to the maintainers opensource@aiven.io.

Trademarks

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

Copyright

Copyright (C) 2018 Aiven Ltd