×
Community Blog The Autonomous and Controllable O&M of Alibaba Cloud MyBase

The Autonomous and Controllable O&M of Alibaba Cloud MyBase

This article describes how to install software, create a privileged account, view database logs, and modify any parameters with Alibaba Cloud MyBase.

By digoal

Background

Is MyBase the RDS providing software installment?

In addition to installing software, you can log in to the host, log in to the real privileged account, and achieve autonomous O&M. At the same time, it supports functions (such as flexible policies).

Here is a demonstration of how to log in to the operating system, install software, create a privileged account, view database logs, modify any parameters, etc.

Create a MyBase Instance

https://www.alibabacloud.com/help/en/apsaradb-for-mybase/latest/what-is-apsaradb-for-mybase

Log in to the Host

On the console, select the host, click the details page, and log in to the host

Install the psql Client

sudo yum install postgresql  
  
[apsaradb@iZbp1h999o4ffut1ydnf7pZ ~]$ which psql  
/usr/bin/psql  
  
[apsaradb@iZbp1h999o4ffut1ydnf7pZ ~]$ psql -V  
psql (PostgreSQL) 9.2.24  

The PG version of the yum repository is relatively old, but more versions will be supported in the future. If you want to install other versions of PG now, you need to upload the lrzsz locally.

You can install software that is not in the yum repository by uploading the software through the lrzsz package and installing it. Alternatively, we can download the software to be installed in ECS first and copy the software on the ECS host to the MyBase host through scp for compilation and installation.

[apsaradb@iZbp1h999o4ffut1ydnf7pZ postgresql-13.2]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        7.5G     0  7.5G   0% /dev
tmpfs           7.5G     0  7.5G   0% /dev/shm
tmpfs           7.5G  980K  7.5G   1% /run
tmpfs           7.5G     0  7.5G   0% /sys/fs/cgroup
/dev/vda1        59G   13G   45G  22% /
/dev/vdb         95G  227M   95G   1% /userdata/data1
/dev/vdc         95G  3.9G   91G   5% /disk17659374
tmpfs           1.5G     0  1.5G   0% /run/user/1000

scp root@172.25.9.61:/root/postgresql-13.2.tar.bz2 ./ 

sudo yum install -y bzip2 readline-devel zlib-devel 

tar -jxvf postgresql-13.2.tar.bz2

cd postgresql-13.2 

./configure --prefix=/userdata/data1/apsaradb/pg13 

make world -j 4
make install-world


cd ~

vi .bash_profile

# Append the following parameters      
export PS1="$USER@`/bin/hostname -s`-> "        
# export PGPORT=1921        
# export PGDATA=/data01/pg13_$PGPORT/pg_root       
      
export LANG=en_US.utf8        
export PGHOME=/userdata/data1/apsaradb/pg13    
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH        
export DATE=`date +"%Y%m%d%H%M"`        
export PATH=$PGHOME/bin:$PATH:.        
export MANPATH=$PGHOME/share/man:$MANPATH        
export PGHOST=$PGDATA        
export PGUSER=postgres        
export PGDATABASE=postgres        
alias rm='rm -i'        
alias ll='ls -lh'        
unalias vi      


[apsaradb@iZbp1h999o4ffut1ydnf7pZ ~]$ . ./.bash_profile 

[apsaradb@iZbp1h999o4ffut1ydnf7pZ ~]$ psql -V
psql (PostgreSQL) 13.2

Log in to the Database Using a Privileged Account

[apsaradb@iZbp1brjbe9m2wv4zla7j7Z ~]$ df -h  
Filesystem      Size  Used Avail Use% Mounted on  
devtmpfs        7.5G     0  7.5G   0% /dev  
tmpfs           7.5G     0  7.5G   0% /dev/shm  
tmpfs           7.5G  944K  7.5G   1% /run  
tmpfs           7.5G     0  7.5G   0% /sys/fs/cgroup  
/dev/vda1        59G   11G   46G  19% /  
/dev/vdb         95G   61M   95G   1% /userdata/data1  
tmpfs           1.5G     0  1.5G   0% /run/user/0  
/dev/vdc         95G  180M   95G   1% /disk17659373  
tmpfs           1.5G     0  1.5G   0% /run/user/1000  
  
  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z ~]$ cd /disk17659373/  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z disk17659373]$ ll  
total 20  
drwx------ 2 root root 16384 Apr  7 17:25 lost+found  
drwxr-xr-x 3 root root  4096 Apr  7 17:29 pgsql  
  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z disk17659373]$ cd pgsql/  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z pgsql]$ ll  
total 4  
drwxr-xr-x 5 root root 4096 Apr  7 17:32 17659373  
  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z pgsql]$ cd 17659373/  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z 17659373]$ ll  
total 12  
drwxr-xr-x  2 root  root     4096 Apr  7 17:32 backup  
drwxr-x--- 20 pgsql apsaradb 4096 Apr  7 17:32 data  
drwx------  3 pgsql root     4096 Apr  7 17:31 log  
  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z 17659373]$ cd data/  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z data]$ ll -la  
total 128  
drwxr-x--- 20 pgsql apsaradb 4096 Apr  7 17:32 .  
drwxr-xr-x  5 root  root     4096 Apr  7 17:32 ..  
drwx------  6 pgsql apsaradb 4096 Apr  7 17:30 base  
-rw-r-----  1 pgsql apsaradb   88 Apr  7 17:29 current_logfiles  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:30 global  
drwxr-x---  2 pgsql apsaradb 4096 Apr  7 17:29 log  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_commit_ts  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_dynshmem  
-rwx------  1 pgsql apsaradb  390 Apr  7 17:29 pg_hba.conf  
-rwx------  1 pgsql apsaradb 1636 Apr  7 17:29 pg_ident.conf  
drwx------  4 pgsql apsaradb 4096 Apr  7 17:32 pg_logical  
drwx------  4 pgsql apsaradb 4096 Apr  7 17:29 pg_multixact  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_notify  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:30 pg_replslot  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_serial  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_snapshots  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_stat  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:42 pg_stat_tmp  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_subtrans  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_tblspc  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_twophase  
-rwx------  1 pgsql apsaradb    3 Apr  7 17:29 PG_VERSION  
drwxr-x---  3 pgsql apsaradb 4096 Apr  7 17:37 pg_wal  
drwx------  2 pgsql apsaradb 4096 Apr  7 17:29 pg_xact  
-rwx------  1 pgsql apsaradb   88 Apr  7 17:29 postgresql.auto.conf  
-rwx------  1 pgsql apsaradb 4037 Apr  7 17:29 postgresql.conf  
-rw-r-----  1 pgsql apsaradb   37 Apr  7 17:29 postmaster.opts  
-rw-r-----  1 pgsql apsaradb   58 Apr  7 17:29 postmaster.pid  
-rwx------  1 pgsql apsaradb 4412 Apr  7 17:29 server.crt  
-rwx------  1 pgsql apsaradb 1679 Apr  7 17:29 server.key  
srwxrwx---  1 pgsql apsaradb    0 Apr  7 17:29 .s.PGSQL.3002  
-rw-r-----  1 pgsql apsaradb   27 Apr  7 17:29 .s.PGSQL.3002.lock  
  
  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z data]$ psql -h `pwd` -p 3002 -U aurora postgres  
psql (9.2.24, server 13.1)  
WARNING: psql version 9.2, server version 13.0.  
         Some psql features might not work.  
Type "help" for help.  

Create a Privileged Account That Can Be Externally Connected

Since aurora is the internal account, pg_hba.conf only allows users to log in locally.

You can create an account that can be logged in externally:

postgres=# \du+  
                                                                       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                            | {}                                                           |   
  
postgres=# create role new_dba encrypted password 'helloWorld#YekMyBase' superuser login;  
CREATE ROLE  
postgres=# \du+  
                                                                       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@iZbp1brjbe9m2wv4zla7j7Z data]$ cd log  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z log]$ ll  
total 328  
-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  
  
  
[apsaradb@iZbp1brjbe9m2wv4zla7j7Z log]$ less postgresql-2021-04-07_172939.csv  

Modify Parameters

Note: The parameters must be modified in both the primary database and the secondary database. Otherwise, the parameters will be unequal after being modified (and even cause failure). For example, some configuration values of standby database parameters must be greater than or equal to the primary database parameters. Some parameters are closely related to system resources. If the parameter modification is too large, the database may fail to start, such as semphor-related parameters.

Primary Database

postgres=# alter system set max_connections=10000;  
ALTER SYSTEM  

Secondary Database

postgres=# alter system set max_connections=10000;  
ALTER SYSTEM  

Some parameters need to be restarted to take effect. You can restart the instance in the console.

You can modify parameters by using alter system or directly editing postgresql.conf and postgresql.auto.conf.

Suggestions

MyBase is an open database service, and users have a wide range of authority, but everything has two sides, so be careful when using these authorities. It may lead to system failure if arbitrarily damaged.

0 0 0
Share on

digoal

255 posts | 19 followers

You may also like

Comments