By digoal
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.
https://www.alibabacloud.com/help/en/apsaradb-for-mybase/latest/what-is-apsaradb-for-mybase
On the console, select the host, click the details page, and log in to the host
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[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. 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 | {} | [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 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.
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.
PostgreSQL Convergence Filtering Optimization for Segment SQL with Multiple Range Conditions
Details of PostgreSQL Bloat Point and Monitoring Metrics – Unrecyclable Garbage Tuples
ApsaraDB - March 4, 2021
ApsaraDB - June 18, 2021
H Ohara - November 20, 2023
ApsaraDB - October 27, 2020
Alibaba Clouder - September 28, 2020
Alibaba Cloud Community - March 4, 2022
Bastionhost
A unified, efficient, and secure platform that provides cloud-based O&M, access control, and operation audit.
Learn More
Managed Service for Grafana
Managed Service for Grafana displays a large amount of data in real time to provide an overview of business and O&M monitoring.
Learn More
ApsaraDB for MyBase
ApsaraDB Dedicated Cluster provided by Alibaba Cloud is a dedicated service for managing databases on the cloud.
Learn More
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreMore Posts by digoal
