How to access the database via tool or code?

Take the RDS for PostgreSQL client as an example to introduce the method for connecting to the instance. For other clients, recommend you to refer to this method.

Note: Recommend to connect to the database using the "psql" tool of PostgreSQL client of Version 9.4.4 and above. Otherwise, the version mismatch leads to unavailability of some features, and the following prompt might occur when connecting:

WARNING: psql major version 9.3, server major version 9.4.4
        Some psql features might not work.

Run the following command using PostgreSQL client, type in the password, and connect to the database according to prompts.

psql -U username -h hostname -p port dbname
Password for user myuser:
psql.bin (9.4.4, server 9.4.4)
Type "help" for help.
dbname=>

Parameter instructions:

• username: Initial account username
• hostname: Instance address
• port: Instance port number
• dbname: Database name for the connection

The following is an example:

How to conduct regular all-around checkup to the RDS for SQL Server instance's resource status?

You can enable the "Cloud Advisor" service to obtain the test reports on security, availability, performance, and cost of cloud resources regularly. In the report, there are a multitude of RDS-related checkup items, such as RDS-instance disabling, RDS-slow SQL report form/diagnosis, RDS-whitelist access risk, RDS-disk full, RDS-idle instance, RDS-high usage rate, RDS-multi-AZ, etc. To learn about or activate the Cloud Advisor service, go to the Cloud Advisor homepage.

How to view the database's parameter information?

Connect to the RDS, and then execute SQL statement show all to view the database's parameter information.

How to view the current database's connection information?

Connect the RDS and then execute SQL statement select * from pg_stat_activity to view the connection information. Subsequently, the result set displays the current connection's database name, user, IP address, connection start time, and statements in the query.

How to view current database's space occupancy?

Connect to the RDS, and then execute SQL statementselect pg_database_size('dbname') for viewing the database's space occupancy.

How to view a table's space occupancy?

Connect to the RDS, and then execute the following SQL statement for viewing the table's space occupancy:

• View the table file size: select pg_size_pretty(pg_relation_size('tablename'))
• View the table index size: select pg_size_pretty(pg_index_size('tablename'))
• View the table size (including index): select pg_size_pretty(pg_total_relation_size('tablename'))

How to resolve the failure to connect to the RDS for PostgreSQL database?

Problem description:

Connect to PostgreSQL database. The rrror report is as follows:

FATAL: The remaining connection slots are reserved for non-replication superuser connections

Cause:

The number of connections is full.

Solution

1. View the limit on the number of current connections

   show max_connections;

2. View connections to the current database

   select * from pg_stat_activity;

3. End the connections using the following SQL

   SELECT pgterminate_backend(_pid)

   Of which, "pid" denotes process ID, namely, the value of "pid" field in "pg_stat_activity" view.

How to deal with the prompt "Current database is being used by active connection(s)" when the RDS for PostgreSQL is deleting the database?

Problem description:

When the RDS for PostgreSQL is deleting the database, the prompt is as follows:

ERROR: Database "test" is being accessed by other users

DETAIL: There is 1 other session using the database.

Cause:

There are connections using the database.

Solutions

Before deleting the database, complete all connections to this database.

View all connections to this database:

SELECT * FROM pg_stat_activity WHERE datname = 'db_name';

Check if the aforesaid connections may be ended. If yes, execute the disconnection:

SELECT pg_terminate_backend(pid) FROM (select pid from pg_stat_activity where datname = 'db_name' ) t;

End all connections and execute:

drop database db_name;

How to deal with the situation that one SQL statement's running time is far more than expected and no results are returned when the current SQL statement is running in the RDS for PostgreSQL?

Cause

Deadlock is more likely to occur in the database.

Check if there is a statement with 'lock' waiting_reason field among the system's current execution statements, by using the following statement:

SELECT * FROM pg_stat_activity;

To make it more distinct, you should use:

SELECT * FROM pg_stat_activity WHERE waiting_reason = ‘lock’;

Query the lock status execution statement.

Solution

First, check if there is a long transaction in the database and if the long transaction blocks other SQL statements. If there are massive tasks in lock state at present, recommend you to finish the corresponding long transaction process:

SELECT pg_terminate_backend(pid)

Of which, "pid" denotes process ID, namely, the value of "pid" field in "pg_stat_activity" view.

How to deal with the situation that space fails to be released after the data gets deleted?

Problem description:

After you clear all data or delete some data using the "delete" statement in PG, the table size keeps unchanged, and the occupied disk space cannot get released.

Cause

In the PG database, the "DELETE" statement does not mean deletion of data records, but simply marks the data as "Invisible". Thus, it does not release the space occupied by the data. Likewise, the "UPDATE" statement does not really mean a modification of original data, but marks original data as "Invisible", and it inserts the updated data records into the end of the table. Therefore, the table, which is subjected to frequent deletion and updating actions, becomes larger and larger.

Solution

1. Recommend you to clear all table data by directly using "TRUNCATE" statement for this statement directly deletes data files in the table and thus space is released immediately;
2. Recommend you to recover invisible space using "VACUUM table_name;" but you just recover the space and make the space available for reuse. However, space is not returned to the operating system (in most cases);
3. To release invisible space, you may take advantage of "VACUUM FULL table_name;". However, this method adds an exclusive lock to the table and consumes some resources. For this reason, We recommend that you operate at the stage of database maintenance.

How to access the database via tool or code?

Take PostgreSQL client as an example to introduce the method for connecting to the instance. For other clients, recommend you to refer to this method.

Note: Recommend you to connect to the database using "psql" tool of PostgreSQL client of Version 9.4.4 and above. Otherwise, the version mismatch leads to unavailability of some features, and the following prompt may appear when connecting:

WARNING: psql major version 9.3, server major version 9.4.4
     Some psql features might not work.

Run the following command using PostgreSQL client, type in the password, and connect to the database according to prompts.

psql -U username -h hostname -p port dbname
Password for user myuser:
psql.bin (9.4.4, server 9.4.4)
Type "help" for help. dbname=>

Parameter instructions:

• username: Initial account username
• hostname: Instance address
• port: Instance port number
• dbname: Database name for the connection

For example: psql -U myuser -h rdsgxxxxxxxx.pg.rds.com -p 3306 dbname

How to view a given database's space occupancy?

Connect to the RDS, and then execute the following SQL statement for viewing the database's space occupancy.

select pg_database_size('dbname'); or select sodddatname, pg_size_pretty(sodddatsize) from gp_toolkit.gp_size_of_database where sodddatname='dbname';

How to view a table's space occupancy?

Connect to the RDS, and then execute the following SQL statement for viewing the table's space occupancy:

• View the table file size:

select pg_size_pretty(pg_relation_size('tablename'))

• View the table index size:

select pg_size_pretty(pg_index_size('tablename'))

• View the table size (including index):

select pg_size_pretty(pg_total_relation_size('tablename'))