A MyBase for PostgreSQL instance in an ApsaraDB for MyBase dedicated cluster supports independent operations and maintenance (O&M). For example, you can install software on a database instance and log on to a database instance by using a privileged account. You can create a privileged account that can be used to log on to a database instance from an external server. You can also view real-time database logs and modify database parameters. This topic shows you how to perform independent O&M on a database instance.

Background information

ApsaraDB for MyBase allows you to create a dedicated cluster so that you can schedule resources and manage hosts and instances in the cluster. ApsaraDB for MyBase enables dedicated cloud resources and resource overcommitment. It also allows you to perform independent O&M and grant users the OS permissions and limited database permissions. ApsaraDB for MyBase provides a custom and optimized database solution for enterprise users. For more information, see Use ApsaraDB for MyBase to create a database that supports independent O&M and auto scaling.

Notes

ApsaraDB for MyBase provides a database service for enterprise users. It allows you to perform independent O&M on databases. However, independent O&M may also cause risks. Proceed with caution because improper operations may cause system failures.

Prerequisites

A MyBase for PostgreSQL instance is created. For more information, see Create a dedicated cluster, Create hosts, and Create an instance in an ApsaraDB for MyBase dedicated cluster.

Log on to a database

  1. Log on to a host that runs a database. For more information, see Access a host by using a webshell.
  2. Run the following command to install the PostgreSQL client:
    sudo yum install postgresql
    Note
    • Before you install the client, grant the host account the permissions to use Yellowdog Updater, Modified (YUM) or RPM Package Manager (RPM). In an ApsaraDB for MyBase dedicated cluster, you can run the sudo yum command as the root user to install the client. For more information about host permissions, see Host permissions.
    • If the version of the PostgreSQL client in the YUM repository is not the latest or expected version, you can install the client in one of the following ways:
      • Use lrzsz to upload the software package from your on-premises machine.
      • Download the software package from an Elastic Compute Service (ECS) instance. You can run SCP commands on the host in ApsaraDB for MyBase. This way, you can copy the software package from the ECS instance and install the software package on the host by compiling the source code.
  3. Log on to the database by using a privileged account.
    1. Run the following command to go to a data directory in the database:
      cd /disk17659373/pgsql/17659373/data/
      Note Sample data directory in the database: /disk17659373/pgsql/17659373/data/. In the directory, 17659373 is randomly generated by the system. To view the actual directory, go to the system root directory.
    2. Run the following command to log on to the database by using a privileged account:
      psql -h `pwd` -p 3002 -U aurora postgres
  4. After you log on to the database, you can perform independent O&M on the database as needed. For more information, see the following topics:

Create a privileged account for external database access

The account aurora is an internal account in the configuration file pg_hba.conf. If you use aurora to log on to a database, you can connect to the database only from your on-premises machine. A MyBase for PostgreSQL instance in ApsaraDB for MyBase allows you to create a privileged account so that you can connect to a database from an external server. This scenario applies if you want to connect to a database from an application by using the database URL.

  1. Query the accounts that you can use to log on to the database.
    Command:
    \du+
    Expected result:
                                                                           List of roles  
             Role name         |                    Attributes                     |                          Member of                           | Description   
    ---------------------------+---------------------------------------------------+--------------------------------------------------------------+-------------  
     aurora                    | Superuser, Replication                            | {}                                                           |   
     pg45667359                | Superuser, Create role, Create DB, Replication    | {}                                                           |   
     pg_execute_server_program | Cannot login                                      | {}                                                           |   
     pg_monitor                | Cannot login                                      | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} |   
     pg_rds_superuser          | Create role, Create DB, Cannot login, Replication | {}                                                           |   
     pg_read_all_settings      | Cannot login                                      | {}                                                           |   
     pg_read_all_stats         | Cannot login                                      | {}                                                           |   
     pg_read_server_files      | Cannot login                                      | {}                                                           |   
     pg_signal_backend         | Cannot login                                      | {}                                                           |   
     pg_stat_scan_tables       | Cannot login                                      | {}                                                           |   
     pg_write_server_files     | Cannot login                                      | {}                                                           |   
     replicator                | Superuser, Replication                            | {}                                                           |   
  2. Run the following command to create a privileged account:
    create role new_dba encrypted password 'helloWorld#YekMyBase' superuser login;
    Note In the command, new_dba indicates the name of the account to create and helloWorld#YekMyBase indicates the account password. The account name and password used in this example are for reference only. Replace them with the actual values. This ensures data security.
  3. Check whether the account is created.
    Command:
    \du+
    Expected result:
                                                                           List of roles  
             Role name         |                    Attributes                     |                          Member of                           | Description   
    ---------------------------+---------------------------------------------------+--------------------------------------------------------------+-------------  
     aurora                    | Superuser, Replication                            | {}                                                           |   
     new_dba                   | Superuser                                         | {}                                                           |   
     pg45667359                | Superuser, Create role, Create DB, Replication    | {}                                                           |   
     pg_execute_server_program | Cannot login                                      | {}                                                           |   
     pg_monitor                | Cannot login                                      | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} |   
     pg_rds_superuser          | Create role, Create DB, Cannot login, Replication | {}                                                           |   
     pg_read_all_settings      | Cannot login                                      | {}                                                           |   
     pg_read_all_stats         | Cannot login                                      | {}                                                           |   
     pg_read_server_files      | Cannot login                                      | {}                                                           |   
     pg_signal_backend         | Cannot login                                      | {}                                                           |   
     pg_stat_scan_tables       | Cannot login                                      | {}                                                           |   
     pg_write_server_files     | Cannot login                                      | {}                                                           |   
     replicator                | Superuser, Replication                            | {}                                                           |   

View real-time logs

ApsaraDB for MyBase allows you to view real-time logs for a MyBase for PostgreSQL instance. You can perform the following steps.

  1. Go to a log directory.
    cd /disk17659373/pgsql/17659373/data/log
    Note Sample log directory: /disk17659373/pgsql/17659373/data/log. In the directory, 17659373 is randomly generated by the system. To view the actual directory, go to the system root directory.
  2. View the log files in the directory.
    Command:
    ll
    Expected result:
    -rw-r----- 1 pgsql apsaradb 111161 Apr  7 17:44 postgresql-2021-04-07_172939.csv  
    -rw-r----- 1 pgsql apsaradb 209440 Apr  7 17:44 postgresql-2021-04-07_172939.log    
  3. Run the following command to view the log file details:
    less postgresql-2021-04-07_172939.csv

Modify a parameter

ApsaraDB for MyBase allows you to modify a parameter for a MyBase for PostgreSQL instance. You can modify a parameter in one of the following ways:
  • Run the alter system command to modify a parameter.
    For example, you can run the following command to modify the value of max_connections to 10000:
    alter system set max_connections=10000;
    Notice
    • This operation is risky. Proceed with caution because improper operations may cause system failures.
    • To modify a parameter, separately log on to the hosts that run the primary and secondary instances. Otherwise, data inconsistency occurs after a primary/secondary switchover is performed between MyBase for PostgreSQL instances in ApsaraDB for MyBase.
    • For some parameters, the parameter values for a secondary database must be greater than or equal to those for the primary database.
    • Some parameters such as semphor are related to system resources. If you improperly modify the parameter values, the database may fail to start.
    • Some parameters do not require an instance restart after you modify the values. The new parameter values take effect in about 5 minutes. However, some parameters require an instance restart before the new values take effect. You can view the information in the ApsaraDB for MyBase console. In the left-side navigation pane, click Parameters. On the page that appears, click the Editable Parameters tab and view the information in the Force Restart column.
  • Edit the configuration file postgresql.conf or postgresql.auto.conf to modify a parameter.
    Notice This operation is risky. Before you edit a configuration file to modify a parameter, back up the file. This way, you can restore the configuration file if improper operations cause system failures.