pgBasEnv is a tool to set environment for community PostgreSQL and EnterpriseDB versions 9.6+.
Check the Change Log for new features and changes introduced in new versions of the tool.
Functionality includes:
- Discover installed homes and data directories
- Print the information about homes and data directories
- Set environment for selected homes or data directories
- Export numerous variables with useful information about current entity
pgBasEnv v1.3 by Trivadis AG
Installation homes:
┌─────────┬─────────┬─────────────────┬─────────────────────────────────┐
│ALIAS │ VER │ OPTIONS │ HOME DIR │
├─────────┼─────────┼─────────────────┼─────────────────────────────────┤
│pgh9615 │ 9.6.15 │ ssl:2G:8K │ /u01/app/postgres/product/9.6.15│
│pgh115 │ 11.5 │ ssl:2G:8K │ /u01/app/postgres/product/11.5 │
│pgh1010 │ 10.10 │ ssl:2G:8K │ /u01/app/postgres/product/10.10 │
│pgh115A │ 11.5 │ ssl:1G:8K │ /usr/pgsql-11 │
│pgh1010A │ 10.10 │ ssl:1G:8K │ /usr/pgsql-10 │
│pgh9615A │ 9.6.15 │ ssl:1G:8K │ /usr/pgsql-9.6 │
│pgh120 │ 12.0 │ ssl:1G:8K │ /usr/pgsql-12 │
└─────────┴─────────┴─────────────────┴─────────────────────────────────┘
Cluster data directories:
┌───────┬───────┬──────┬───────┬─────────┬───────┬─────────────────────────┬──────────────────┬──────────────────────────────────┐
│ALIAS │ VER │ STAT │ PORT │ PID │ SIZE │ PGDATA │ LAST START │ LAST START HOME │
├───────┼───────┼──────┼───────┼─────────┼───────┼─────────────────────────┼──────────────────┼──────────────────────────────────┤
│pgd96 │ 9.6 │ UP │ 5436 │ 25107 │ 39M │ /u02/pgdata/tbx06 │ 2020-02-13 16:28 │ /u01/app/postgres/product/9.6.15 │
│pgd10 │ 10 │ DOWN │ │ │ 40M │ /u02/pgdata/newdb/data │ │ │
│pgd10A │ 10 │ DOWN │ │ │ 40M │ /u02/pgdata/newdb/data2 │ │ │
│pgd12 │ 12 │ UP │ 5437 │ 25309 │ 41M │ /u02/pgdata/tbx07 │ 2020-02-13 16:30 │ /usr/pgsql-12 │
│pgd10B │ 10 │ UP │ 5435 │ 31296 │ 56M │ /u02/pgdata/tbx05 │ 2020-04-24 18:33 │ /u01/app/postgres/product/10.10 │
│pgd96A │ 9.6 │ UP │ 5433 │ 18854 │ 39M │ /u02/pgdata/tbx03 │ 2020-04-22 12:23 │ /usr/pgsql-9.6 │
│pgd11 │ 11 │ DOWN │ │ │ 104M │ /u02/pgdata/tbx01 │ 2020-02-13 16:21 │ /usr/pgsql-11 │
│pgd11A │ 11 │ DOWN │ │ │ 40M │ /var/lib/pgsql/11/data │ 2020-04-22 16:12 │ /u01/app/postgres/product/11.5 │
│pgd10C │ 10 │ DOWN │ │ │ 40M │ /var/lib/pgsql/10/data │ 2019-09-26 15:07 │ /usr/pgsql-10 │
└───────┴───────┴──────┴───────┴─────────┴───────┴─────────────────────────┴──────────────────┴──────────────────────────────────┘
---[pgd12]:
Cluster name: tbx07
Installation home: /usr/pgsql-12
Cluster data directory: /u02/pgdata/tbx07
Cluster port: 5437
Cluster status: UP
Cluster version: 12
Cluster start time: 2020-02-13 16:30
Size of all tablespaces: 25MB
Cluster archive mode: off
Cluster age: 14
Autovacuum status: ACTIVE
Cluster databases: postgres,template1,template0
---[08.05.2020 13:00]
The pgBasEnv is released under the APACHE LICENSE, VERSION 2.0, that allows a collaborative open source software development.
There are few main folders:
-- bin
|
-- etc
|
-- bundle
|
-- scripts
Folders bin and etc are relevant for developers.
Folder bundle includes ready to install bundle of the current tool version.
Usually bundle folder includes tar file and installer script.
If you will fork this repository to make modifications, then you should modify scripts in bin directory. After all modifications are done, then use bundle.sh script to create a new ready to install bundle in the bundle folder.
Folder scripts used to store any useful scripts. All scripts in this folder will be available from psql over special variables. Check the description on this page and readme.txt inside scripts folder.
There is installer script available in bundle directory to make installation process fast and easy. It has also silent execution mode which can be used for bulk installations.
To execute installation two files required:
install_pgbasenv.shpgbasenv-[version].tar
Both files must be in the same directory.
Installer script, which will do the installation and upgrade. It can be executed without arguments to do regular installation:
$ ./install_pgbasenv.sh
If it is fresh installation for current user, then script will be executed in interactive mode.
It will ask to provide the installation location for the pgBasEnv. This directory will be the base directory for the TVD tools and will be indicated with variable $TVDBASE.
The value entered can also include variables, like $MYSCRIPTS/pgs.
Default value for $TVDBASE is $HOME/tvdtoolbox.
Inside $TVDBASE directory pgbasenv will be created. It will be the home for pgBasEnv and will be indicated with variable $PGBASENV_BASE.
Following two important environment files will be created in users home directory $HOME:
.PGBASENV_HOME.pgbasenv_profile
File .PGBASENV_HOMEwill be used as entry point for pgBasEnv.
File .pgbasenv_profile can be used to source pgBasEnv global environment. It will be added by default to the users ~/.pgsql_profile during installation. At next login pgBasEnv is sourced automatically.
Note: The installer adds the entry to .pgsql_profile because .bash_profile is overwritten anytime when a new PostgreSQL binary is installed (e.g. with yum install) and pgBasEnv would not be sourced automatically at login.
To prevent the filesystem scan on each login, you can update .pgbasenv_profile file and add --noscan option to the pgsetenv.sh call.
Installer will ask for the following parameters which can affect the discovery process:
| Parameter | Default value | Description |
|---|---|---|
PGBASENV_EXCLUDE_DIRS |
"tmp proc sys" |
The list of root level directories to skip during the discovery process. |
PGBASENV_EXCLUDE_FILESYSTEMS |
"nfs tmpfs" |
The list of filesystem types to skip during the discovery process. |
PGBASENV_SEARCH_MAXDEPTH |
7 |
Maximum directory search depth during the discovery process. |
After providing values for these variables or accepting defaults, installer will discover all PostgreSQL or EnterproseDB installations and cluster data directories.
Each home and data directory will be assigned an alias name.
The list of all found homes and data directories will be printed, then user can choose the default alias name to set default environment. This environment will be set by default when you first log in.
The variable name is PGBASENV_INITIAL_ALIAS, if it will be left null, which is default, then pgBasEnv will set default alias automatically. Default alias will be identified according to next steps:
- Alias of the data directory with latest version of the up and running clusters.
- Alias of the data directory with latest version of the not running clusters.
- Alias of the installation home with latest version.
If it is not the fresh installation, then script will use the already configured $TVDBASE/pgbasenv location and will updated scripts to the new versions. User specific config files will not be updated or modified.
Just execute ./install_pgbasenv.sh from bundle direcotry. Latest version tar file will be installed by default.
To overwrite upgrade mode force option can be used. Then, installation script will behave like in fresh installation mode.
$ ./install_pgbasenv.sh --force
Silent mode can be used in scripts.
With silent mode all the parameters can be provided on command line, if some will be omitted then defaults will be used.
Example:
$ ./install_pgbasenv.sh --silent TVDBASE=/u01/pgsql PGBASENV_EXCLUDE_DIRS="proc u04" PGBASENV_SEARCH_MAXDEPTH=10
Silent option will be executed in force mode.
$TVDBASE --
|
|-- pgbasenv --
|
|-- bin --
| |
| |-- pgbasenv.sh
| |-- pgsetenv.sh
| |-- pgup.sh
| |-- pgstatus.sh
| |-- scriptmgr.sh
|
|-- etc --
|-- pgbasenv.conf
|-- pgbasenv_standard.conf
|-- pgclustertab
|-- pghometab
|-- *.env
Description of the scripts:
| Script | Func | Alias | Description |
|---|---|---|---|
pgbasenv.sh |
pgbasenv |
Will do discovery and create/update pghometab and pgclustertab files. | |
pgup.sh |
pgup |
u |
Will print the information from tab files and add runtime information to it. |
pgsetenv.sh |
pgsetenv |
Sourcing script, it will set the global and object level environment. | |
pgstatus.sh |
pgstatus |
sta |
Will print the information about currently set environment. |
scriptmgr.sh |
Will be used to expose $PGBASENV_BASE/scripts contents to psql. Not for explicit execution. |
Each script will be exported to the current shell over the functions (Func column), it will make it possible to access them fast and from any location.
Like:
$ pgbasenv --force
Because they are exported as a functions they will be also accessible from sub-shells. Generally it is recommended to use functions inside the scripts and aliases on the terminal.
Some of the mostly executed scripts has also shortcuts in form of aliases, like u and sta, to list all resources and print the current object status.
Folder ./etc includes configuration files and tab files.
| File | Description |
|---|---|
pgbasenv.conf |
File to set user specific variables. It can be modified by the user. It will be sourced to the environment after the pgbasenv_standard.conf. Will not be modified during upgrade mode. |
pgbasenv_standard.conf |
Variables and aliases delivered by developer. Must not be modified, will be overwritten during upgrade mode. |
pghometab |
File which includes the list of all found installation homes and their properties including alias. Will not be modified during upgrade. |
pgclustertab |
List of all found cluster data directories and their properties including aliases. Will not be modified during upgrade. |
*.env |
Environment file for the alias. User can create [alias].env file to set alias specific environment |
Two tab files will be generated in the $PGBASENV_BASE/etc :
pghometabpgclustertab
Has the following format:
HOME;VERSION;[OPTIONS WITH_SSL:SEGMENT_SIZE:BLOCK_SIZE];ALIAS
It is the information, discovered by pgbasenv. Main field separator is ;, the third column is composite column and includes three sub-columns delimited with :, these are: WITH_SSL indicated if home was with SSL support built, SEGMENT_SIZE second sub-column indicates the segment size set during the build and last third one BLOCK_SIZE indicates block size.
Column ALIAS will include the automatically generated alias name for the home.
The alias format is pgh[VERSION], for example if there is home version 11.5 then alias will be pgh115, if there are two 11.5 homes installed, then second one will be pgh115A, and so on.
User can set its own alias for the homes. pgbasenv will not overwrite the aliases.
Has the following format:
PGDATA;VERSION;HOME;PORT;ALIAS
Alias naming here is similar to the home alias naming, but with prefix pgd, like for data directory version 11 it will be pgd11 and if the will be another one then pgd11A and so on.
In pgclustertab there are three columns which can be modified by the user: HOME, PORT and ALIAS.
All three will not be overwritten by pgbasenv.
The pgup will list the pgclustertab contents and will merge it with the runtime information.
If, for example, pgd11 alias has port defined as 5433 in the tab file but cluster is up and running from this data directory and actually has port 5445 then, pgup will show actual port 5445.
To discover the current installations and data directories pgbasenv can be used.
First discovery will be executed during installation.
To execute discovery again, in case if some new home was installed or new cluster was created, then pgbasenv can be manually executed.
Every execution of pgbasenv discovers PostgreSQL binary installations as well as database clusters based on the $PGDATA directory. By default, it will update existing pghometab and pgclustertab files. The update will add new discovered entries but not delete any formerly existing entry. This means, if for example a certain $PGDATA directory is temporarily not accessible because of an ongoing restore or standby reinstate operation a concurrent pgbasenv execution will not delete this cluster database entry from pgclustertab. There is a dedicated parameter --clean-pgclustertab to delete non-existing database cluster entries from pgclustertab.
$ pgbasenv --clean-pgclustertab
There is option to forcefully re-create both tab files from scratch with --force option.
$ pgbasenv --force
Discovery script will check the current user, if it will be enterprisedb then pgBasEnv will consider current environment as EnterpriseDB installation, in all other cases it will be treated as community version PostgreSQL.
To change this behaviour, user can export PGBASENV_VENDOR variable before installation or discovery process. Like this:
$ export PGBASENV_VENDOR=enterprisedb
$ pgbasenv
The value of the PGBASENV_VENDOR can be enterprisedb or postgres.
To list current installation homes and data directories use pgup function or u alias.
It will print out the user friendly tables, see example on top of this document.
It is possible to get the output in script fredly format, just add --list argument:
$ pgup --list
pgd96;9.6;UP;5436;25107;39M;/u02/pgdata/tbx06;2020-02-13 16:28;/u01/app/postgres/product/9.6.15
pgd10;10;DOWN;;;40M;/u02/pgdata/newdb/data;;
pgd12;12;UP;5437;25309;41M;/u02/pgdata/tbx07;2020-02-13 16:30;/usr/pgsql-12
pgd10B;10;UP;5435;31296;56M;/u02/pgdata/tbx05;2020-04-24 18:33;/u01/app/postgres/product/10.10
The output is only for data directories and includes also runtime information.
To set environment from the terminal special aliases can be used.
Each alias in the pgup output has associated shell alias.
For example:
Cluster data directories:
===============================================================================================================================
ALIAS | VER | STAT | PORT | PID | SIZE | PGDATA | LAST START | LAST START HOME
===============================================================================================================================
pgd96 | 9.6 | UP | 5436 | 25107 | 39M | /u02/pgdata/tbx06 | 2020-02-13 16:28 | /u01/app/postgres/product/9.6.15
pgd10 | 10 | DOWN | | | 40M | /u02/pgdata/newdb/data | |
pgd10A | 10 | DOWN | | | 40M | /u02/pgdata/newdb/data2 | |
pgd12 | 12 | UP | 5437 | 25309 | 41M | /u02/pgdata/tbx07 | 2020-02-13 16:30 | /usr/pgsql-12
pgd10B | 10 | UP | 5435 | 31296 | 56M | /u02/pgdata/tbx05 | 2020-04-24 18:33 | /u01/app/postgres/product/10.10
pgd96A | 9.6 | UP | 5433 | 18854 | 39M | /u02/pgdata/tbx03 | 2020-04-22 12:23 | /usr/pgsql-9.6
You can set environment for the /u02/pgdata/tbx07 directory, just enter its alias on the shell prompt pgd12:
node1:/usr/home/pg $ pgd12
---[pgd12]:
Cluster name: tbx07
Installation home: /usr/pgsql-12
Cluster data directory: /u02/pgdata/tbx07
Cluster port: 5437
Cluster status: UP
Cluster version: 12
Cluster start time: 2020-02-13 16:30
Size of all tablespaces: 25MB
Cluster archive mode: off
Cluster age: 14
Autovacuum status: ACTIVE
Cluster databases: postgres,template1,template0
---[08.05.2020 17:04]
node1:/usr/home/pg [pgd12]$ _
Note: If cluster is not running from the data directory at the moment of environment sourcing and PORT was not explicitly defined in
pgclustertabthen PGPORT will be set to 1. We are doing it because if it will be set to null then libpq will use default port 5432 (or whatever configured during build). In this case, there can be some other cluster which is already running on this port. That means all database tools will connect to the incorrect cluster. By setting it to 1 we prevent such dangerous behavior. After you will start the cluster you must source environment for the database cluster to actualize its status and port information. But the best way to prevent such situations is to define the port explicitly in pgclustertab file.
Environment will be set and the output from pgstatus will be printed out.
Each time when environment set for the specific alias, pgsetenv will check $PGBASENV_BASE/etc folder for the env file named after alias.
When we set environment for pgd12 then file $PGBASENV_BASE/etc/pgd12.env will be also sourced. When you will switch environment to some other alias, then pgd12.env will be unset from the environment.
After setting data directory alias, alias per database will be also created. Database aliases has format db.(db name), like db.salesdb.
Database aliases will export PGDATABASE with appropriate value and call psql.
Bash prompt will also set to include current alias name.
It is possible to combine data directory alias with installation home alias.
On example above pgd10, has no installation home associated, most probably that it was never started. To start using particular installation home, for example home pgh1010, you can combine both of them like this:
$ pgd10 pgh1010
Then you can manipulate your cluster using binaries from pgh1010 home.
The script responsible for setting environment is pgsetenv.
It stays behind the above described aliases.
It is possible to directly execute pgsetenv function from the shell or inside the scripts.
Examples:
$ pgsetenv pgd10
$ pgsetenv pgd10 pgh1010
It is also possible to set environment by data directory, like this:
$ pgsetenv /u02/pgdata/tbx05
Can be used inside the scripts, eliminates lookup for the alias name.
Another option for pgsetenv is the argument --default. When executed with this argument, the environment for default alias will be set. It will identified based on variable PGBASENV_INITIAL_ALIAS in the $PGBASENV_BASE/etc/pgbasenv.conf file.
By default pgsetenv will scan the filesystem to find any new installations and data directories. Sometimes it is better to source without scanning, in this case use --noscan option.
pgsetenv will source following resources to the current shell:
| Name | Type | Description |
|---|---|---|
TVDBASE |
Variable | Base directory for TVD Tools. |
PGBASENV_BASE |
Variable | Base directory for pgBasEnv |
TVDPGOPERATE_BASE |
Variable | Base directory for TVD-PgOperate |
pglogfile |
function | Will print the current logfile of the Logging Collector |
pgunsetenv |
function | To unset global environment |
pgunsetclsenv |
function | To unset alias specific environment |
If pgsetenv will be executed without any arguments then it will set the global environment. Which includes:
- The variables and aliases from pgbasenv_standard.conf
- The variables and aliases from pgbasenv.conf
TVD_PGUP_CLUSTERSvariable, the list of aliases of the UP clusters
If pgsetenv will be executed for specific home alias, then following variables will be set:
| Name | Description |
|---|---|
PGBASENV_ALIAS |
Current alias name |
TVD_PGHOME |
Installation home directory |
PGLOCALEDIR |
Will be set to $TVD_PGHOME/share/locale |
EDBHOME |
In case of EnterpriseDB will be set to installation home directory |
Variable $MANPATH will be updated to include $TVD_PGHOME/share/man.
Variable $PATH will be updated to include $TVD_PGHOME/bin.
For the data directory alias the following variables will be set:
If data directory has associated home directory, then all variables for the home will be set. As mentioned above.
Variables will be set, if it will be possible to discover them, if not, they will not be set.
| Variable | Description |
|---|---|
TVD_PGCLUSTER_NAME |
Cluster name |
PGDATA |
Data directory |
PGPORT |
Current port |
PGSQL_BASE |
PgOperate related. If parameters_.conf file exists, then PGSQL_BASE from it will be sourced |
PGBASENV_ALIAS |
Current alias name |
TVD_PGSTATUS |
The status, UP or DOWN |
TVD_PGVERSION |
Data directory version |
TVD_PGSTART_TIME |
Last time cluster was started |
TVD_PGIS_STANDBY |
Is standby or not, YES or NO |
TVD_PGIS_INRECOVERY |
Is standby in recovery or note, YES or NO |
TVD_PGMASTER_HOST |
Master host name |
TVD_PGMASTER_PORT |
Master port |
TVD_PGSTANDBY_STATUS |
Status of the wal reveiver process from pg_stat_wal_receiver. |
TVD_PGCLUSTER_SIZE |
The size of all tablespaces in human readable format |
TVD_PGCLUSTER_DATABASES |
List of all databases in cluster |
TVD_PGUSER_DATABASES |
List of all user created database in cluster |
TVD_PGAUTOVACUUM_STATUS |
Status of the autovacuum, ACTIVE or NOTACTIVE |
TVD_PGCLUSTER_AGE |
The transaction age of the cluster |
TVD_PGLOG_COLLECTOR |
Is Logging Collector enabled or nor, on or off |
TVD_PGLOG_DIR |
Log directory |
TVD_PGLOG_FILE |
The logfile pattern |
TVD_PGCONF |
The config file of the cluster. postgresql.conf. |
TVD_PGHBA |
The hba conf file pg_hba.conf |
TVD_PGARCHIVE_MODE |
Is cluster in archive log mode, on or off |
To get the current logfile name, use function pglogfile, if logging collector is enabled then it will return the name of the logfile:
$ cat $(pglogfile)
Use sta alias or pgstatus function to print the values of the some this variables on the screen. The values of some variables will be printed in red or green depending on the value. For example if autovacuum is not active then it will be red, if active then green.
There are also some very useful aliases, like vic which will open postgresql.conf with vi for you.
Check the $PGBASENV_BASE/etc/pgbasenv_standard.conf for the predefined variables and aliases.
You can define you own or overwrite defaults one in $PGBASENV_BASE/etc/pgbasenv.conf file.
Each script in this directory will be available in the psql under special variables.
Copy any script into this directory, then call psql from any location.
Enter the variable ":scripts" in psql prompt. It will list you all the scripts available for current PostgreSQL version.
postgres=# :scripts
pgBaseEnv scripts:
┌───────────────────────────────┬─────────────┬──────────────────────────────────────────────────────────────────────────┐
│ Name (Variable) │ Min. Version│ Description │
├───────────────────────────────┼─────────────┼──────────────────────────────────────────────────────────────────────────┤
│ :myscript1 │ * │ Script description here │
│ :myscript2 │ 12 │ Second script description │
└───────────────────────────────┴─────────────┴──────────────────────────────────────────────────────────────────────────┘
Count: 2
It will be enough to enter ":myscript1" to execute the script.
postgres=# :myscript1
You can put special descriptors in your scripts:
-- NAME: <script_name_without_space> (Max length 30 characters)
-- VERSION: <minimum major PostgreSQL version supported for this script> (Must be a number)
-- DESCRIPTION: <description of the script> (Max length 73 characters)
Example:
-- NAME: index_stats
-- VERSION: 12
-- DESCRIPTION: Collect postgres index stats.
select ....
from ....
where ....;
This script will not be listed in psql version 11, but will be available in version 12 and later.
If NAME descriptor will not be found or null, then script name, without ".sql" part will be used.
If VERSIONS descriptor will not be found or null, then script will be available on all versions.
If DESCRIPTION descriptor will not be found or null, then no description will be displayed.
To execute from command line use pipe, -c will not work:
$ echo ":myscript1" | psql
All of the listed above variables can be accessed from the scripts also.
If you write the script which will be executed in some other shell and not in the current one, the you will need first source pgbasenv.
Then you can set environment based on alias or data directory.
For example you want to start backup on standby site only, then your script will look like this:
# Source pgbasenv
source $HOME/.pgbasenv_profile
# Call pgsetenv to set environment for your data directory
# You can use data directory itself
pgsetenv /u02/pgdata/tbx05
# or its alias
pgsetenv pg12
# Now all the variables, functions and aliases are available for you
if [[ $TVD_PGIS_STANDBY == "YES" ]]; then
pg_basebackup -D /u01/backup
fi