Common DBA Tasks for PostgreSQL

This section describes the Amazon RDS implementations of some common DBA tasks for
DB instances
running the PostgreSQL database engine. To deliver a managed service experience, Amazon
RDS
doesn't provide shell access to DB instances, and it restricts access to certain system
procedures and tables that require advanced privileges.

Creating Roles

When you create a DB instance, the master user system account that you create is
assigned to the rds_superuser role. The rds_superuser role is a
predefined Amazon RDS role similar to the PostgreSQL superuser role (customarily named
postgres in local instances), but with some restrictions. As with the
PostgreSQL superuser role, the rds_superuser role has the most privileges for
your DB instance. You should not assign this role to users unless they need the most
access to
the DB instance.

Manage tablespaces, including creating and deleting them. For more information, see
the Tablespaces section in the PostgreSQL documentation.

View all users not assigned the rds_superuser role using the
pg_stat_activity command and kill their connections using the
pg_terminate_backend and pg_cancel_backend
commands.

Grant and revoke the rds_replication role for all roles that are not the
rds_superuser role. For more information, see the GRANT section in
the PostgreSQL documentation.

The following example shows how to create a user and then grant the user the
rds_superuser role. User-defined roles, such as
rds_superuser, have to be granted.

create role testuser with password 'testuser' login;
CREATE ROLE
grant rds_superuser to testuser;
GRANT ROLE

Managing PostgreSQL Database Access

In Amazon RDS for PostgreSQL, you can manage which users have privileges to connect
to which
databases. In other PostgreSQL environments, you sometimes perform this kind of management
by
modifying the pg_hba.conf file. In Amazon RDS, you can use database grants
instead.

New databases in PostgreSQL are always created with a default set of privileges. The
default privileges allow PUBLIC (all users) to connect to the database and to
create temporary tables while connected.

To control which users are allowed to connect to a given database in Amazon RDS, first
revoke
the default PUBLIC privileges. Then grant back the privileges on a more granular
basis. The following example code shows how.

For more information about privileges in PostgreSQL databases, see the
GRANT
command in the PostgreSQL documentation.

Working with PostgreSQL
Parameters

PostgreSQL parameters that you set for a local PostgreSQL instance in the
postgresql.conf file are maintained in the DB parameter group for your
DB instance. If you create a DB instance using the default parameter group, the parameter
settings are in the parameter group called default.postgres9.6.

When you create a DB instance, the parameters in the associated DB parameter group
are loaded. You can modify parameter values by changing values in the parameter group.
You can
also change parameter values, if you have the security privileges to do so, by using
the ALTER
DATABASE, ALTER ROLE, and SET commands. You can't use the command line
postgres command or the env PGOPTIONS command, because you have no
access to the host.

Keeping track of PostgreSQL parameter settings can occasionally be difficult. Use
the
following command to list current parameter settings and the default value.

For an explanation of the output values, see the pg_settings topic in the
PostgreSQL documentation.

If you set the memory settings too large for max_connections,
shared_buffers, or effective_cache_size, you will prevent the
PostgreSQL instance from starting up. Some parameters use units that you might not
be familiar
with; for example, shared_buffers sets the number of 8-KB shared memory buffers
used by the server.

The following error is written to the postgres.log file when the
instance is attempting to start up, but incorrect parameter settings are preventing
it from
starting.

There are two types of PostgreSQL parameters, static and dynamic. Static parameters
require that the DB instance be rebooted before they are applied. Dynamic parameters
can be
applied immediately. The following table shows parameters that you can modify for
a PostgreSQL
DB instance and each parameter's type.

Parameter Name

Apply_Type

Description

application_name

Dynamic

Sets the application name to be reported in statistics and logs.

array_nulls

Dynamic

Enables input of NULL elements in arrays.

authentication_timeout

Dynamic

Sets the maximum allowed time to complete client authentication.

autovacuum

Dynamic

Starts the autovacuum subprocess.

autovacuum_analyze_scale_factor

Dynamic

Number of tuple inserts, updates, or deletes before analyze as a fraction of
reltuples.

autovacuum_analyze_threshold

Dynamic

Minimum number of tuple inserts, updates, or deletes before analyze.

autovacuum_naptime

Dynamic

Time to sleep between autovacuum runs.

autovacuum_vacuum_cost_delay

Dynamic

Vacuum cost delay, in milliseconds, for autovacuum.

autovacuum_vacuum_cost_limit

Dynamic

Vacuum cost amount available before napping, for autovacuum.

autovacuum_vacuum_scale_factor

Dynamic

Number of tuple updates or deletes before vacuum as a fraction of
reltuples.

autovacuum_vacuum_threshold

Dynamic

Minimum number of tuple updates or deletes before vacuum.

backslash_quote

Dynamic

Sets whether a backslash (\) is allowed in string literals.

bgwriter_delay

Dynamic

Background writer sleep time between rounds.

bgwriter_lru_maxpages

Dynamic

Background writer maximum number of LRU pages to flush per round.

bgwriter_lru_multiplier

Dynamic

Multiple of the average buffer usage to free per round.

bytea_output

Dynamic

Sets the output format for bytes.

check_function_bodies

Dynamic

Checks function bodies during CREATE FUNCTION.

checkpoint_completion_target

Dynamic

Time spent flushing dirty buffers during checkpoint, as a fraction of the
checkpoint interval.

Sets the planner's estimate of the cost of a non-sequentially fetched disk
page.

rds.log_retention_period

Dynamic

Amazon RDS will delete PostgreSQL logs that are older than N minutes.

search_path

Dynamic

Sets the schema search order for names that are not schema-qualified.

seq_page_cost

Dynamic

Sets the planner's estimate of the cost of a sequentially fetched disk
page.

session_replication_role

Dynamic

Sets the sessions behavior for triggers and rewrite rules.

sql_inheritance

Dynamic

Causes subtables to be included by default in various commands.

ssl_renegotiation_limit

Dynamic

Sets the amount of traffic to send and receive before renegotiating the
encryption keys.

standard_conforming_strings

Dynamic

Causes ... strings to treat backslashes literally.

statement_timeout

Dynamic

Sets the maximum allowed duration of any statement.

synchronize_seqscans

Dynamic

Enables synchronized sequential scans.

synchronous_commit

Dynamic

Sets the current transactions synchronization level.

tcp_keepalives_count

Dynamic

Maximum number of TCP keepalive retransmits.

tcp_keepalives_idle

Dynamic

Time between issuing TCP keepalives.

tcp_keepalives_interval

Dynamic

Time between TCP keepalive retransmits.

temp_buffers

Dynamic

Sets the maximum number of temporary buffers used by each session.

temp_tablespaces

Dynamic

Sets the tablespaces to use for temporary tables and sort files.

timezone

Dynamic

Sets the time zone for displaying and interpreting time stamps.

track_activities

Dynamic

Collects information about executing commands.

track_counts

Dynamic

Collects statistics on database activity.

track_functions

Dynamic

Collects function-level statistics on database activity.

track_io_timing

Dynamic

Collects timing statistics on database I/O activity.

transaction_deferrable

Dynamic

Indicates whether to defer a read-only serializable transaction until it can be
executed with no possible serialization failures.

transaction_isolation

Dynamic

Sets the current transactions isolation level.

transaction_read_only

Dynamic

Sets the current transactions read-only status.

transform_null_equals

Dynamic

Treats expr=NULL as expr IS NULL.

update_process_title

Dynamic

Updates the process title to show the active SQL command.

vacuum_cost_delay

Dynamic

Vacuum cost delay in milliseconds.

vacuum_cost_limit

Dynamic

Vacuum cost amount available before napping.

vacuum_cost_page_dirty

Dynamic

Vacuum cost for a page dirtied by vacuum.

vacuum_cost_page_hit

Dynamic

Vacuum cost for a page found in the buffer cache.

vacuum_cost_page_miss

Dynamic

Vacuum cost for a page not found in the buffer cache.

vacuum_defer_cleanup_age

Dynamic

Number of transactions by which vacuum and hot cleanup should be deferred, if
any.

vacuum_freeze_min_age

Dynamic

Minimum age at which vacuum should freeze a table row.

vacuum_freeze_table_age

Dynamic

Age at which vacuum should scan a whole table to freeze tuples.

wal_writer_delay

Dynamic

WAL writer sleep time between WAL flushes.

work_mem

Dynamic

Sets the maximum memory to be used for query workspaces.

xmlbinary

Dynamic

Sets how binary values are to be encoded in XML.

xmloption

Dynamic

Sets whether XML data in implicit parsing and serialization operations is to be
considered as documents or content fragments.

autovacuum_freeze_max_age

Static

Age at which to autovacuum a table to prevent transaction ID wraparound.

autovacuum_max_workers

Static

Sets the maximum number of simultaneously running autovacuum worker
processes.

max_connections

Static

Sets the maximum number of concurrent connections.

max_files_per_process

Static

Sets the maximum number of simultaneously open files for each server
process.

max_locks_per_transaction

Static

Sets the maximum number of locks per transaction.

max_pred_locks_per_transaction

Static

Sets the maximum number of predicate locks per transaction.

max_prepared_transactions

Static

Sets the maximum number of simultaneously prepared transactions.

shared_buffers

Static

Sets the number of shared memory buffers used by the server.

ssl

Static

Enables SSL connections.

temp_file_limit

Static

Sets the maximum size in KB to which the temporary files can grow.

track_activity_query_size

Static

Sets the size reserved for pg_stat_activity.current_query, in bytes.

wal_buffers

Static

Sets the number of disk-page buffers in shared memory for WAL.

Amazon RDS uses the default PostgreSQL units for all parameters. The following table
shows the
PostgreSQL default unit and value for each parameter.

Parameter Name

Unit

effective_cache_size

8 KB

segment_size

8 KB

shared_buffers

8 KB

temp_buffers

8 KB

wal_buffers

8 KB

wal_segment_size

8 KB

log_rotation_size

KB

log_temp_files

KB

maintenance_work_mem

KB

max_stack_depth

KB

ssl_renegotiation_limit

KB

temp_file_limit

KB

work_mem

KB

log_rotation_age

minutes

autovacuum_vacuum_cost_delay

ms

bgwriter_delay

ms

deadlock_timeout

ms

lock_timeout

ms

log_autovacuum_min_duration

ms

log_min_duration_statement

ms

max_standby_archive_delay

ms

max_standby_streaming_delay

ms

statement_timeout

ms

vacuum_cost_delay

ms

wal_receiver_timeout

ms

wal_sender_timeout

ms

wal_writer_delay

ms

archive_timeout

s

authentication_timeout

s

autovacuum_naptime

s

checkpoint_timeout

s

checkpoint_warning

s

post_auth_delay

s

pre_auth_delay

s

tcp_keepalives_idle

s

tcp_keepalives_interval

s

wal_receiver_status_interval

s

Working
with PostgreSQL Autovacuum on Amazon RDS

We strongly recommend that you use the autovacuum feature for PostgreSQL databases
to
maintain the health of your PostgreSQL DB instance. Because autovacuum checks for
tables that
have had a large number of inserted, updated, or deleted tuples, you can use autovacuum
to
prevent transaction ID wraparound. Autovacuum automates the execution of the VACUUM
and the
ANALYZE command. Using autovacuum is required by PostgreSQL, not imposed by Amazon
RDS, and its use
is critical to good performance. The feature is enabled by default for all new Amazon
RDS
PostgreSQL DB instances, and the related configuration parameters are appropriately
set by
default. Since our defaults are somewhat generic, you can benefit from tuning parameters
to
your specific workload. This section can help you perform the needed autovacuum tuning.

Maintenance Work Memory

One of the most important parameters influencing autovacuum performance is the maintenance_work_mem parameter. This parameter determines how
much memory you allocate for autovacuum to use to scan a database table and to hold
all the
row IDs that are going to be vacuumed. If you set the value of the maintenance_work_mem parameter too low, the vacuum process might
have to scan the table multiple times to complete its work, possibly impacting
performance.

When doing calculations to determine the maintenance_work_mem parameter value, keep in mind two
things:

The default unit is KB for this parameter.

The maintenance_work_mem parameter works in conjunction with the
autovacuum_max_workers parameter. If you have many small
tables, allocate more autovacuum_max_workers and less maintenance_work_mem. If you have large tables (say, larger
than 100 GB), allocate more memory and fewer workers. You need to have enough memory
allocated to succeed on your biggest table. Each autovacuum_max_workers can use the memory you allocate, so you
should make sure the combination of workers and memory equal the total memory you
want
to allocate.

In general terms, for large hosts, set the maintenance_work_mem parameter to a value between one and two
gigabytes. For extremely large hosts, set the parameter to a value between two and
four
gigabytes. The value you set for this parameter should depend on the workload. Amazon
RDS
has updated its default for this parameter to be
GREATEST({DBInstanceClassMemory/63963136*1024},65536).

Determining if the Tables in Your Database Need Vacuuming

A PostgreSQL database can have two billion "in-flight" unvacuumed transactions before
PostgreSQL takes dramatic action to avoid data loss. If the number of unvacuumed
transactions reaches (2^31 - 10,000,000), the log will start warning that vacuuming
is
needed. If the number of unvacuumed transactions reaches (2^31 - 1,000,000), PostgreSQL
sets
the database to read only and requires an offline, single-user, standalone vacuum.
This
requires multiple hours or days (depending on size) of downtime. A very detailed explanation
of TransactionID wraparound is found in the PostgreSQL documentation.

The following query can be used to show the number of unvacuumed transactions in a
database. The datfrozenxid column of a database's pg_database row is
a lower bound on the normal XIDs appearing in that database; it is
the minimum of the per-table relfrozenxid values within the
database.

When the age of a database hits two billion, TransactionID (XID) wraparound occurs
and
the database will go into read only. This query can be used to produce a metric and
run a
few times a day. By default, autovacuum is set to keep the age of transactions to
no more
than 200,000,000 (autovacuum_freeze_max_age).

A sample monitoring strategy might look like this:

Autovacuum_freeze_max_age is set to 200 million.

If a table hits 500 million unvacuumed transactions, a low-severity alarm is triggered.
This
isn’t an unreasonable value, but it could indicate that autovacuum isn’t keeping
up.

If a table ages to one billion, this should be treated as an actionable alarm. In
general, you
want to keep ages closer to autovacuum_freeze_max_age for performance reasons.
Investigation using the following steps is recommended.

If a table hits 1.5 billion unvacuumed transactions, a high-severity alarm is triggered.
Depending on how quickly your database uses XIDs, this alarm can indicate that the
system is running out of time to run autovacuum and that you should consider immediate
resolution.

If a table is constantly breaching these thresholds, you need further modify your
autovacuum parameters. By default, VACUUM (which has cost-based delays disabled) is
more
aggressive than default autovacuum, but, also more intrusive to the system as a
whole.

We have the following recommendations:

Be aware and enable a monitoring mechanism so that you are aware of the age of your
oldest
transactions.

For busier tables, perform a manual vacuum freeze regularly during a maintenance window
in
addition to relying on autovacuum. For information on performing a manual vacuum freeze,
see
Performing a Manual Vacuum Freeze.

Determining Which Tables
Are Currently Eligible for Autovacuum

Often, it is one or two tables in need of vacuuming. Tables whose relfrozenxid value
is
more than autovacuum_freeze_max_age transactions old are always targeted by autovacuum.
Otherwise, if the number of tuples made obsolete since the last VACUUM exceeds the
"vacuum
threshold", the table is vacuumed.

Determining if
Autovacuum Is Currently Running and For How Long

If you need to manually vacuum a table, you need to determine if autovacuum is currently
running. If it is, you might need to adjust parameters to make it run more efficiently,
or
terminate autovacuum so you can manually run VACUUM.

Use the following query to determine if autovacuum is running, how long it has been
running,
and if it is waiting on another session.

If you are using Amazon RDS PostgreSQL 9.6+ or higher, use this query:

Several issues can cause long running (multiple days) autovacuum session. The most
common
issue is that your maintenance_work_mem parameter value is set too low for the size
of the table or rate of updates.

It can indicate that there aren't enough autovacuum_max_workers for your workload.
You will
need to indicate the number of workers.

It can indicate that there is an index corruption (autovacuum will crash and restart
on the
same relation but make no progress). You will need to run a manual vacuum freeze verbose
___table___ to see the exact cause.

Performing a Manual Vacuum Freeze

You might want to perform a manual vacuum on a table that has a vacuum process already
running. This is useful if you have identified a table with an "XID age" approaching
2
billion (or above any threshold you are monitoring).

The following steps are a guideline, and there are several variations to the process.
For example, during testing, you find that the maintenance_work_mem parameter value was set too small and that
you need to take immediate action on a table but don't want to bounce the instance
at the
moment. Using the queries listed above, you determine which table is the problem and
notice
a long running autovacuum session. You know you need to change the maintenance_work_mem parameter setting, but you also need to take
immediate action and vacuum the table in question. The following procedure shows what
you
would do in this situation:

To manually perform a vacuum freeze

Open two sessions to the database containing the table
you want to vacuum. For the second session, use "screen"
or another utility that maintains the session if your
connection is dropped.

In session one, get the PID of the autovacuum session running on the table. This action
requires that you are running Amazon RDS PostgreSQL 9.3.12 or later, 9.4.7 or later,
or 9.5.2
or later to have full visibility into the running rdsadmin processes.

Run the following query to get the PID of the autovacuum session.

SELECT datname, usename, pid, waiting, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY
xact_start;

In session two, calculate the amount of memory you will need for this operation. In
this
example, we determine that we can afford to use up to 2 GB of memory for this operation,
so we set maintenance_work_mem for the current session to 2 GB.

set maintenance_work_mem='2 GB';
SET

In session two, issue a vacuum freeze verbose for the table. The verbose setting is
useful
because, although there is no progress report for this in PostgreSQL currently, you
can
see activity.

In session one, if autovacuum was blocking, you will see in pg_stat_activity
that waiting is "T" for your vacuum session. In this case, you need to terminate the
autovacuum process.

select pg_terminate_backend('the_pid');

At this point, your session begins. It's important to note that
autovacuum will restart
immediately as this table is probably the highest on its list of work. You will need
to
initiate your command in session 2 and then terminate the autovacuum process in session
one.

Reindexing a Table When
Autovacuum Is Running

If an index has become corrupt, autovacuum will continue to process the table and
fail.
If you attempt a manual vacuum in this situation, you will receive an error message
similar
to the following:

When the index is corrupted and autovacuum is attempting to run against the table,
you
will contend with an already running autovacuum session. When you issue a "REINDEX "
command, you will be taking out an exclusive lock on the table and write operations
will be
blocked as well as reads that use that specific index.

To reindex a table when autovacuum is running on the table

Open two sessions to the database containing the table
you want to vacuum. For the second session, use "screen"
or another utility that maintains the session if your
connection is dropped.

In session one, get the PID of the autovacuum session running on the table.
This action requires that you are running Amazon RDS PostgreSQL 9.3.12 or later,
9.4.7 or later, or 9.5.2 or later to have full visibility into the running
rdsadmin processes.

Run the following query to get the PID of the autovacuum session:

SELECT datname, usename, pid, waiting, current_timestamp - xact_start
AS xact_runtime, query
FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY
xact_start;

In session one, if autovacuum was blocking, you will see in pg_stat_activity that waiting is "T" for your vacuum session. In
this case, you will need to terminate the autovacuum process.

select pg_terminate_backend('the_pid');

At this point, your session begins. It's important to note that autovacuum will restart
immediately as this table is probably the highest on its list of work. You will need
to
initiate your command in session 2 and then terminate the autovacuum process in session
one.

Other Parameters
That Affect Autovacuum

This query will show the values of some of the parameters that directly impact
autovacuum and its behavior. The autovacuum parameters are described fully in the PostgreSQL documentation.

Table-Level Parameters

Autovacuum related storage parameters can be set at a table level, which can be better than
altering the behavior of the entire database. For large tables, you might need to
set
aggressive settings and you might not want to make autovacuum behave that way for
all
tables.

This query will show which tables currently have table level options in place:

select relname, reloptions
from pg_class
where reloptions is not null;

An example where this might be useful is on tables that are much larger than the rest
of your tables. If you have one 300-GB table and 30 other tables less than 1 GB, you
might
set some specific parameters for your large table so you don't alter the behavior
of
your entire system.

alter table mytable set (autovacuum_vacuum_cost_delay=0);

Doing this disables the cost-based autovacuum delay for this table at the expense
of
more resource usage on your system. Normally, autovacuum pauses for
autovacuum_vacuum_cost_delay each time autovacuum_cost_limit is reached. You can find
more
details in the PostgreSQL documentation about cost-based vacuuming.

Autovacuum Logging

By default, the postgresql.log doesn't contain information about
the autovacuum process. If you are using PostgreSQL 9.4.5 or later, you can see output
in
the PostgreSQL error log from the autovacuum worker operations by setting the
rds.force_autovacuum_logging_level parameter. Allowed values are
disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error,
log, fatal, and panic. The default value is disabled
because the other allowable values can add significant amount of information to your
logs.

We recommend that you set the value of the
rds.force_autovacuum_logging_level parameter to log and that
you set the log_autovacuum_min_duration parameter to a value from 1000 or
5000. If you set this value to 5000, Amazon RDS writes activity to the log that takes
more
than five seconds and shows "vacuum skipped" messages when application locking is
causing
autovacuum to intentionally skip tables. If you are troubleshooting a problem and
need
more detail, you can use a different logging level value, such as debug1 or
debug3. Use these debug parameters for a short period of time because these
settings produce extremely verbose content written to the error log file. For more
information about these debug settings,
see the
PostgreSQL documentation.

NOTE: PostgreSQL version 9.4.7 and later includes improved visibility of autovacuum
sessions by allowing the rds_superuser account to view autovacuum sessions in
pg_stat_activity. For example, you can identify and terminate an autovacuum
session that is blocking a command from running, or executing slower than a manually
issued vacuum command.

Audit Logging for a PostgreSQL DB Instance

There are several parameters you can set to log activity that occurs on your PostgreSQL
DB
instance. These parameters include the following:

The log_statement parameter can be used to log user activity in your PostgreSQL
database. For more information, see PostgreSQL Database Log Files.

The rds.force_admin_logging_level parameter logs actions by the RDS internal user
(rdsadmin) in the databases on the DB instance, and writes the output to the PostgreSQL
error log. Allowed values are disabled, debug5, debug4, debug3, debug2, debug1, info,
notice, warning, error, log, fatal, and panic. The default value is disabled.

The rds.force_autovacuum_logging_level parameter logs autovacuum worker
operations in all databases on the DB instance, and writes the output to the
PostgreSQL error log. Allowed values are disabled, debug5, debug4, debug3,
debug2, debug1, info, notice, warning, error, log, fatal, and panic. The
default value is disabled. The Amazon RDS recommended setting for
rds.force_autovacuum_logging_level: is LOG. Set log_autovacuum_min_duration
to a value from 1000 or 5000. Setting this value to 5000 will write activity
to the log that takes more than 5 seconds and will show "vacuum skipped"
messages. For more information on this parameter, see Best Practices for Working with PostgreSQL.

Working with the pgaudit
Extension

The pgaudit extension provides detailed session and object audit logging for
Amazon RDS for PostgreSQL version 9.6.3 and later and version 9.5.7 version and later.
You can
enable session auditing or object auditing using this extension.

With session auditing, you can log audit events from various sources and includes
the
fully qualified command text when available. For example, you can use session auditing
to log
all READ statements that connect to a database by setting pgaudit.log to 'READ'.

With object auditing, you can refine the audit logging to work with specific commands.
For
example, you can specify that you want audit logging for READ operations on a specific
number
of tables.

To use object based logging with the pgaudit extension

Create a specific database role called rds_pgaudit. Use the following command to
create the role.

CREATE ROLE rds_pgaudit;
CREATE ROLE

Modify the parameter group that is associated with your DB instance to use the shared
preload
libraries that contain pgaudit and set the parameter
pgaudit.role. The pgaudit.role must be set to the role
rds_pgaudit.

Working with the pg_repack
Extension

You can use the pg_repack extension to remove bloat from tables and
indexes. This extension is supported on Amazon RDS for PostgreSQL versions 9.6.3 and
later. For
more information on the pg_repack extension, see the GitHub project documentation.

To use the pg_repack extension

Install the pg_repack extension on your Amazon RDS for PostgreSQL DB
instance by running the following command.

CREATE EXTENSION pg_repack;

Use the pg_repack client utility to connect to a database. Use a database role that
has
rds_superuser privileges to connect to the database. In the
following connection example, the rds_test role has
rds_superuser privileges, and the database endpoint used is
rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com.

Working with PostGIS

PostGIS is an extension to PostgreSQL for storing and managing spatial information.
If you
are not familiar with PostGIS, you can get a good general overview at PostGIS
Introduction.

You need to perform a bit of setup before you can use the PostGIS extension. The following
list shows what you need to do; each step is described in greater detail later in
this
section.

Connect to the DB instance using the master user name used to create the DB instance.

Load the PostGIS extensions.

Transfer ownership of the extensions to therds_superuser role.

Transfer ownership of the objects to the rds_superuser role.

Test the extensions.

Step 1: Connect to the DB
Instance Using the Master User Name Used to Create the DB Instance

First, you connect to the DB instance using the master user name that was used to
create
the DB instance. That name is automatically assigned the rds_superuser role.
You need the rds_superuser role that is needed to do the remaining
steps.

The following example uses SELECT to show you the current user; in this case, the
current user should be the master username you chose when creating the DB instance.

select current_user;
current_user
-------------
myawsuser
(1 row)

Step 2: Load the PostGIS Extensions

Use the CREATE EXTENSION statements to load the PostGIS extensions. You must also
load
the extension. You can then use the \dnpsql command to list the owners of the PostGIS schemas.

Using pgBadger for Log Analysis with PostgreSQL

You can use a log analyzer such as pgbadger to analyze PostgreSQL logs. The pgbadger
documentation states that the %l pattern (log line for session/process) should be
a part of
the prefix. However, if you provide the current rds log_line_prefix as a parameter
to
pgbadger it should still produce a report.

Viewing the Contents of
pg_config

In PostgreSQL version 9.6.1, you can see the compile-time configuration parameters
of the
currently installed version of PostgreSQL using the new view pg_config. You can use
the view
by calling the pg_config function as shown in the following sample.

Working with the orafce
Extension

The orafce extension provides functions that are common in commercial
databases, and can make it easier for you to port a commercial database to PostgreSQL.
Amazon RDS
for PostgreSQL versions 9.6.6 and later support this extension. For more information
about
orafce, see the orafce project on
GitHub.

Note

Amazon RDS for PostgreSQL doesn't support the utl_file package that is part of the orafce extension.
This is because the utl_file schema functions provide read and write operations on operating-system text files,
which requires superuser access to the underlying host.

To use the orafce extension

Connect to the DB instance with the master user name that you used to create the DB
instance.

Note

If you want to enable orafce on a different database in the same instance, use
the /c dbname psql command to change from the master database after
initiating the connection.

Enable the orafce extension with the CREATE EXTENSION statement.

CREATE EXTENSION orafce;

Transfer ownership of the oracle schema to the rds_superuser role with the ALTER
SCHEMA statement.

ALTER SCHEMA oracle OWNER TO rds_superuser;

Note

If you want to see the list of owners for the oracle schema, use the \dn psql command.

Accessing External Data with the postgres_fdw Extension

You can access data in a table on a remote database server with the postgres_fdw
extension. If you set up a remote connection from your PostgreSQL DB instance, access
is also
available to your Read Replica.

Enabling Custom DNS Resolution

To enable the DNS resolution in your customer VPC, you need to associate a custom
DB
parameter group to your RDS PostgreSQL instance, turn on the parameter
rds.custom_dns_resolution by setting it to 1, and restart the DB
instance for the changes to take place.

Disabling Custom DNS Resolution

In order to disable the DNS resolution in your customer VPC, you need to turn off
the
parameter rds.custom_dns_resolution of your custom DB
parameter group by setting it to 0, then restart the DB instance for the changes to
take
place.

Setting Up a Custom DNS Server

After you set up your custom DNS name server, it takes up to 30 minutes to propagate
the
changes to your DB instance. After the changes are propagated to your DB instance,
all
outbound network traffic requiring a DNS lookup queries your DNS server over port
53.

To set up a custom DNS server for your Amazon RDS PostgreSQL DB instance, do the following:

From the DHCP options set attached to your VPC,
set the domain-name-servers option to the IP address of your DNS name server.
For more information, see
DHCP Options Sets.

Note

The domain-name-servers option accepts up to four values,
but your Amazon RDS DB instance uses only the first value.

Configure your DNS server to allow inbound traffic
from your Amazon RDS DB instances over port 53.
If your DNS server is in an Amazon VPC,
the VPC must have a security group that contains inbound rules
that allow UDP and TCP traffic on port 53.
If your DNS server is not in an Amazon VPC,
it must have appropriate firewall whitelisting
to allow UDP and TCP inbound traffic on port 53.