How to add remote to GIT repo

Git remote provide information to sync with a Git repository to do stuff like git fetch, git push, git pull.

This information is stored in .git/config. In the case of a new directory, that does not have git, start by configuring it with the command git init. Managing remote can be done by the following commands.

Adding a remote

git remote add <name> <url>

Example

git remote add git_user1 user1@git.myserver.local/repo/package.git

git remote add ssh_user1 ssh://user1@10.1.1.100/repo/package.git

View the current remote and detailed remote information

git remote

git remote -v

Remove a remote

git remote remove <name>

git remote rm <name>

Example

git remote rm remote_user1

Push changes of the specific remote

git push <name>

Example

git push ssh_user1

or to push as a new branch

git push -u ssh_user1 new_branch

Show log of the remote

git remote show <name>

Example

git remote show ssh_user1

Show status of CURRENT branch

git status

Change url of an existing remote

git remote set-url <name> <new url>

Example

git remote set-url remote_user1 ssh://user1@10.1.1.23/repo/package.git

Basic of SQL joins

SQL join statements are used to combine rows from two or more tables, based on related column(s) between those tables.  These statements assist user to extract data from tables which have one-to-many or many-to-many relationships between them.

Here is a basic list of join command examples as used in PostgreSQL database.

GIT tag and retag

Working with GIT allows tagging specific points along the repository that have some importance. Commonly, tag is used when a version is released. Here are examples of listing tags, adding and deleting a tag.

List tags

List tag on local

git tag -l "v1.*"

git tag

List tag on repository

git ls-remote --tags

Display details of a tag

git show v1.0.2

Add tag to current branch

git tag -a v1.0.2 HEAD -m "Update for version 1.0.2"

git push origin --tags

Tag can be added to a specific commit.

git tag -a v1.0.2 f8c3501-m "Update for version 1.0.2" 

Retagging

This requires deleting current tag, then publish changes to remote repository.

git tag -d v1.0.2

git push origin :refs/tags/v1.0.2

How to create a dynamic object from standard class

 PHP provides a class to create a temporary object where no specific class and members are required.

The class stdClass is the empty class in PHP used to cast other types to object. Among the example of stdClass usage;

1. Directly access the members by calling them
2. Dynamic objects can be provided for temporary usage

E.g. 

An array can be treated as an object by using stdClass. 

$tmpStudent = array(

"name" => "John Doe"

);

To access data, 

$tmpStudent['name']

$tmpStudent = new stdClass;

$tmpStudent->name = "John Doe";

Using the stdClass, this can be done as an object

$tmpStudent->name

Reportserver query with wildcards

The community edition of Reportserver.net provide a large number of useful functions. This include managing user access, download in different formats, user freedom to customise the report and access from different databases.

Here is an example of string query with wildcard and parameters. The $P{email} is a parameter where you can enter a value.

SELECT user_id, email, registration_id, date, venue, type, status

FROM student_registration

WHERE email LIKE '%'||$P{email}||'%'

Another example to apply parameter ${timezone} which is the default handling of variable, in this case to add with date from created_at

SELECT user_id, email, registration_id, created_at::timestamp + ${timezone}::interval as created_at

FROM student_registration

Centos Stream 8 with Selinux and high CPU

In a server with only 1 CPU, there are times where Linux service fight to gain usage of resources. However, Selinux strict policies may cause a service to repeatedly demand usage that lead to 100% CPU usage.

Several ways to diagnose, which would include to determine the max connections and process running. The commands include

To view number of connection on the web server

ss -ant | grep -E ':80|:443' 

systemctl status httpd

To view number of processes running, sorted by CPU or memory.

top

Laravel and search for json content in postgresql database

This is a note on usage of JSON in Laravel 8.

Given a Postgresql database with a table name customer, that have a column name status it would look like this

status=[
{"id": 5, "name": "registered", "description": "Registered only","date":"20221002 013600}
]

The function to search for JSON includes;

whereJsonContains

orWhereJsonContains

Laravel sample code to retrieve all rows where status is either 2, 5, or 6 would look like this;

$filter = [2,5,6];

$customers = Customers::where(function ($q) use ($filter) {

    foreach ($filter as $val) {

        $q->orWhereJsonContains('customers.status', [['id' => $val]]);

    }

})->get( );

Kali Linux on MS Windows 10

MS Windows 10 have been an interesting change for Microsoft operating system, and maybe MS Windows 11 will build on its capability to work with more operating systems.

Here is how to update MS Windows 10 to run Kali Linux, a Debian based Linux operating system. Its the basic system, and supports installation of additional Linux tools. Among default tools available are

• BruteShark - Network Forensic Analysis Tool (NFAT)
• Evil-WinRM - Ultimate WinRM shell
• Hakrawler - Web crawler designed for easy, quick discovery of endpoints and assets
• Httpx - Fast and multi-purpose HTTP toolkit
• LAPSDumper - Dumps LAPS passwords
• PhpSploit - Stealth post-exploitation framework
• PEDump - Dump Win32 executable files
• SentryPeer - SIP peer-to-peer honeypot for VoIP
• Sparrow-wifi - Graphical Wi-Fi Analyzer for Linux
• wifipumpkin3 - Powerful framework for rogue access points

On MS Windows, the WSL2 component needs to be downloaded and configured.

Firstly, open Powershell prompt as Administrator.

In the prompt type,

Enable-WindowsOptionalFeature -Online -FeatureName VirtualMachinePlatform -norestart 

dism.exe /online /enable-feature /featurename:Microsoft-Windows-Subsystem-Linux /all

Download and install (double click the file and follow the instructions) the wsl_update for your specific computer architecture. e.g. x64. Reboot the computer.

open Powershell prompt as Administrator.

In the prompt type,

wsl --set-default-version 2

Open microsoft app store and install Kali Linux (https://aka.ms/wslstore). Click get, then Open. A command prompt screen will appear and start to initialise.

Enter a default username (without spaces or special characters). Then enter the password and verify.

By default, Kali Linux installs dbus-x11 to access Linux desktop.

Install a user desktop

On debian systems, the command apt-get installs additional packages to provide more tools and features. XFCE is a light weight desktop that provide a windows like environment to run applications, instead of the command line prompt only.

Its fun to run Linux commands on the Verify that you can access internet and install additional Linux apps with the following command

sudo apt-get install wget

Lets download the XFCE desktop and install

sudo apt-get install kali-defaults kali-root-login desktop-base xfce4 xfce4-places-plugin xfce4-goodies

sudo apt install -y kali-desktop-xfce xrdp

When asked to select the “Default display manager”, choose lightdm

Now, its time to go get some coffee after the following command as the download size is over 1.2Gb. But keep an eye on the terminal as it will prompt for your input. 

Choosing different desktops

If you have other desktops, the default can be shown by

update-alternatives --config x-session-manager

After the install is done, start the desktop 

During installation, troubleshooting: 

There is an error "kali-desktop-base_2022.4.5_all.deb  404  Not Found", it may be due to the default Kali not able to find the updated URL of resources. Run the following commands;

apt-get update

apt-get install kali-defaults kali-root-login desktop-base xfce4 xfce4-places-plugin xfce4-goodies --fix-missing

Dont know which XRDP port?

sudo lsof -i -P -n | grep LISTEN

Access Linux desktop on MS Windows

On a regular Kali Linux, command to start xfce is "systemctl enable xrdp --now". But the WSL version provided doesnt support this. Lets configure xfce for larger screens, then start xfce. 

cp /etc/xrdp/xrdp.ini /etc/xrdp/xrdp.ini.backup

sed -i 's/3389/3390/g' /etc/xrdp/xrdp.ini

sed -i 's/max_bpp=32/#max_bpp=32\nmax_bpp=128/g' /etc/xrdp/xrdp.ini

sed -i 's/xserverbpp=24/#xserverbpp=24\nxserverbpp=128/g' /etc/xrdp/xrdp.ini

/etc/init.d/xrdp start

To stop the desktop

sudo /etc/init.d/xrdp stop

This will result in a port number being use for the desktop access. Refe to the file /etc/xrdp/xrdp.ini for details. E.g. port 3390.

From the Windows Start, Open up Remote Desktop (mstsc) and connect to localhost:3390

Login with the username you provided at the installation stage.

View incoming packets

Login to Kali Linux command prompt,

apt-get install pktstat

pktstat -n

Access USB device (not working)

Install the required 3rd party packages. At this moment, I have only managed to get it working on Ubuntu. Will update for Kali in future if there is a solution.

The following command should display detected disk, however Kali failed as MS Windows 10 did not have USB pass through. 

fdisk -l

Open wsl, the default ubuntu and install the usb tools

sudo apt-get update &&  apt install linux-tools-common  linux-tools-generic hwdata

Login to Kali Linux

winget install --interactive --exact dorssel.usbipd-win

apt-get install usbip

Install top 10 Kali tools

A list of top 10 tools is available, which takes over 2.2Gb storage size. The command is

apt-get install kali-tools-top10

Ref: 

Kali.org

USBIPD

XDA USB

Another XDA USB

Tinker and Controller methods

 Tinker is the tool for command line debugging in Laravel. 

To start the tool, type

php artisan tinker

Create an object from Model

In tinker command line, objects can be created and saved to database. E.g.

$user = new App\Model\User

$user->name = "Tboxmy"

$user->email = "tboxmy@yahoo.com"

$user->save()

Then to display the content of object;

$user

Finding data from model

The eloquent function find( ) allows retrieving from the database, given its default index id. e.g.

$user = App\Model\User::find(2)

Then to display the content of object;

$user

Another approach is to use the function where( ) to retrieve based on the table column names. e.g.

$user = App\Model\User::where('name','Tboxmy')->first( )

Or to retrieve all those related values

$user = App\Model\User::where('id', '>', 2)->first( )

Then to display the content of object;

$user

Introduction to app() and how to call a method in a controller?

A helper object app( ) allows access to the different functions to access a model or controller. The call( ) function is one of the app( ) functions, it is used to access a method inside a controller. e.g. in the controller have a method declared as following;

public function methodName($id=0, $name=null){

. . .

}

The function call( ) can then be used this way;

app()->call(' App\Http\Controllers\HomeController@methodName');

When working with different Controllers, its useful to declare these as variables using the make( ) function. Here is how its done;

$controller = app()->make('App\Http\Controllers\HomeController');

app()->call([$controller, 'methodName'] );

How to pass parameters to methods in a controller? 

This is done with the function call( ) where parameter 2 contains the method parameters. Using the method above, here is how its done;

app()->call([$controller, 'methodName'], [ 'id' => 2, 'name' => 'Tboxmy'] );

Save linux output to jpg or image file

 There are cases where from the Linux terminal, it is required to post the output as an attachment in jpg or png. If ImageMagick is installed, this can be done easily, otherwise they use the traditional command pr to provide the output to printer.

Pre-requisite

Install ImageMagick

Combine the command convert with the output. E.g. output of the route command can be saved as png

route  | convert label:@- myroutetable.png

Next to add data into the png image.

$ convert myroutetable.png \

>  -set 'Copyright' 'CC-BY-SA 4.0'\

>  -set 'Title' 'Routing Table for Linux' \

> routetable.png

The content can then be viewed by the command hexdump

hexdump -C routetable.png

Or

strings routetable.png

Find files by data and type in Linux

 How do I find files in a Linux system? or specifically on Centos Linus?

The command ls will list files in a specific directory. Using the asterisk (*) its possible to display list of files and directory with wildcards. 

E.g.

ls -l

ls -lt

ls -lrt

ls /etc

ls /etc/h*

Search files with command grep and ls

ls -lt /etc/ | grep filename

Search files with command find

find directory -type f -name filename

The command find provide more flexibility in search for files.

Search ordinary files that has the extension .cpp

find /home/you -iname "*.cpp" -type f

Search ordinary files that has the extension .cpp that are more than 30 days

find /home/you -iname "*.cpp" -atime -30 -type f

Search files from a given modified date

find . -type f -newermt 2023-01-01

Search files from a given access date

find . -type f -newerat 2017-09-25 ! -newerat 2017-09-26

Search files given a date range

touch --date "2007-01-01" /tmp/start.file

touch --date "2008-01-01" /tmp/end.file

find /data/images -type f -newer /tmp/start.file -not -newer /tmp/end.file

Copy Postgresql database to another server

 Notes for Postgresql 11,12

This approach allows an SQL file to be copied to a destination server and install using that sql file.

On the source server

pg_dump -C -Fp -f dump.sql -U postgres database_name

Copy the file dump.sql to the destination server. On the destination server

psql -U postgres -f dump.sql

SQL Query with JSON column in Postgresql

I still use JSON datatypes in databases, and its proving to its worth. Postgresql loads the whole row during queries and this reduce access to other tables for join and retains data, instead of relying on dynamic changes of another table. JSONB type column is supported by Reportserver.net which I have mentioned in previous post. 

Here is an example to use JSONB type column in Postgresql 12 and newer. Difference from previous post, here its saved as an object instead of an array. Use of JSON or JSONB type is very much determined by the data intended for storage and retrieval.

Points to note:

Symbol ->

meaning: Get JSON array element (indexed from zero, negative integers count from the end)

Or Get JSON object field by key

Symbol: ->>

meaning: Get JSON array element as text Or Get JSON object field as text

Symbol: @>

meaning: Does the left JSON value contain the right JSON path/value entries at the top level?

Step 1: Create the sample database

CREATE TABLE public.products (

id serial NOT NULL,

"name" varchar(255) NOT NULL,

unit varchar NOT NULL, -- limit to 2 decimals

product jsonb NOT NULL,

created_at timestamp(0) NULL,

updated_at timestamp(0) NULL,

CONSTRAINT products_pkey PRIMARY KEY (id)

);

Step 2: Add sample data

Step 3: Run an SQL query

Display list of data as is.

SELECT id, name, product from products;

id|name       |product                                                                                                |

--|-----------|-------------------------------------------------------------------------------------------------------|

 3|Apple Pie  |{"id": 5, "name": "Apple Pie", "details": {"value": "1All-1-1000-xzp", "source": "Malaysia"}}          |

 1|Orange Bod |{"id": 18, "name": "Orange Gala", "details": {"value": "1Bll-1-99-aaa", "source": "Malaysia"}}         |

 2|Chicken Pie|{"id": 4, "name": "Downtown Chicken Pie", "details": {"value": "1Bll-1-201-aaa", "source": "Malaysia"}}|

Display columns id, name and product->details

SELECT id, name, product->'details' from products;

or

SELECT id, name, product->'details' as details from products;

Display columns id, name and sub contents of a json data

select id, name, (product->'details')::jsonb->'source' as details from products;

The WHERE clause

Lets retrieve rows where jsonb element key 'name' have a specific value.

SELECT id, name, product->'details' as details from products

where product->>'name'= 'Apple Pie'

Retrieve rows where jsonb element key 'name' have a wildcard (%) value.

select id, name, product->'details' as details from products

where product->>'name' like 'Apple %'

Retrieve rows where jsonb sub element key 'value' have a wildcard (%) value.

select id, name, product->'details' as details from products

where product->'details'->>'value'::text like  '1All-1-%'

Retrieve rows where jsonb element have a specific key:value

select id, name,  product as details from products

where product @> '{"name":"Orange Gala"}'

I hope these examples will benefit you.

Read copy update the RCU

On Linux, listing of processes will most likely show a process rcu_sched with tty having value of ? Here is an example of a command

ps -ef | grep rcu

UID PID PPID C STIME TTY TIME CMD

root 3 2 0 Jul06 ? 00:00:00 [rcu_gp]
root 4 2 0 Jul06 ? 00:00:00 [rcu_par_gp]
root 10 2 0 Jul06 ? 00:00:00 [rcu_tasks_rude_]
root 11 2 0 Jul06 ? 00:00:00 [rcu_tasks_trace]
root 13 2 0 Jul06 ? 00:03:37 [rcu_sched]

This is a solution on Linux where shared data structures is accessed by many resources. When this data structure encounter an update, those resources at different stages of access need to ensure it is "lock" safe when pointing to specific reference of that data structure.

Anyone encountering errors that mention rcu_sched can refer to kernel.org

Updating Centos Application related to security

While a Centos Linux server version is still supported, its application security updates is provided by the distro and its related repository. Application security updates are maintained by most large projects such as Apache HTTPD 2.4 and Supervisor.

Side note, there is a nice article on use of CentOS Stream at crunchtools.

Few things that can be done.

Identify the installed Centos.

uname -r

cat /etc/redhat-release

View latest security advisory CVEs.

yum updateinfo list cves

sudo dnf updateinfo list security

sudo dnf list-security

If above information doesn't show any applications, then there are no security related updates. General updates is viewed with

sudo dnf updateinfo list

Check update for HTTPD. As shown below, there is an available package for update, but no CVE is ragged..

dnf check-update httpd

dnf info httpd

In this next example, there is a CVE tagged to the update. 

dnf check-update supervisor

dnf info supervisor

Information on the update is available.

dnf updateinfo info FEDORA-EPEL-2021-1ad3a13e05

View installed notes on application updates.

rpm -q --changelog httpd

rpm -q --changelog supervisor

The said changes can then be updated

sudo dnf update supervisor

Howto redirect HTTP to HTTPS in Apache httpd

 Apache web server is an open source software (OSS) that is widely used on many sites even though there are a few newer web servers. 

HTTP request and response are transmitted over the network in plain text format. Anyone with a network sniffing tools or a man-in-the-middle (MitM) tool can easily intercept and read the information. Widely used internet search engine, Google began to use HTTPS as a search ranking signal in 2014. 

HTTPS request and response are transmitted using TLS or SSL, that encrypts the data travelling on the network. Those special tools are able to capture the data, but because it is encrypted, its of not much use to the average hackers and intruders. Using HTTPS provide advantages over HTTPS, such as;

1. Data is encrypted, making it safer to transmit sensitive information over the network.
2. Modern web browsers are better able to inform users if the site is safe in many aspects. Such as having a valid certificate or if data is coming from where its suppose to be instead of some unidentifiable source.
3. Use of modern HTTP/2 protocol provide a better user experience, which includes improved site performance and security.
4. Free wifi hotspots that tend to inject advertisements can be prevented.
5. Web browsers can advice and prevent users from accessing data sensitive features such as geolocation, device motion and orientation if the site isn't HTTPS

Looking forward: 

1. Legacy web browsers such as MS Internet Explorer do not have compatible SSL 2.0 and TLS 1.2. Which is why there is a need to disable its SSL 2.0 features when visiting HTTPS sites. (MS KB 2851628)
2. Malware have started to ride on HTTPS encrypted features. Which means more advanced network monitoring tools are required to manage such malware.

In order to ensure web browsers are only using HTTPS, any request that is HTTP must be forwarded to a HTTP request. This is done with Apache httpd directive "Redirect".

E.g. where our host is harmonyshades.com.

Step 1: Edit Apache configuration file and include this. 

<VirtualHost *:80>

  ServerName harmonyshades.com

  

  Redirect / https://harmonyshades.com

</VirtualHost>

Step 2: Restart wen server

systemctl restart httpd

Test on a web browser. HTTP pages will automatically load HTTPS pages.

Edit Postgresql configuration without restart of database

Postgresql common configuration files are the postgresql.conf and pg_hba.conf. In many cases, its possible to edit without the need to restart the database. Here are the 2 options on  Centos Linux;

Use systemctl

# systemctl reload postgresql-11

or

# systemctl reload postgresql

Within Postgresql

Login and and access psql as admin or user postgres then run this sql.

SELECT pg_reload_conf();

Check Postgresql connections

Postgresql database provides 2 main configurations to limit the incoming connections. This is done in the file postgresql.conf

max_connections = 200                   # (change requires restart)
superuser_reserved_connections = 3      # (change requires restart)

Notes here refers to Postgresql version 11.

Following are common sql commands to monitor connections.

List number of connections

SELECT count(distinct(numbackends)) 
  FROM pg_stat_database; 

List connections by database

SELECT datname, numbackends 
  FROM pg_stat_database; 

    datname     | numbackends
----------------+-------------
 postgres      |           1
 template1     |           0
 template0     |           0
 shop01        |           0
 tutorial_php  |           0
 tutorial_linux|           0
 helpdesk      |          27
 telegram      |           6
(8 rows)

List the connections on a specific database

SELECT * FROM pg_stat_activity 
  WHERE datname='helpdesk';

List number of session that is active or idle.

SELECT state, count(*) FROM pg_stat_activity  

  WHERE pid <> pg_backend_pid() 

  GROUP BY 1 

  ORDER BY 1;

        state        | count

---------------------+-------

 active              |    55

 idle                |    38

 idle in transaction |     2

                     |     5

(4 rows)

These will provide data for planning and risk mitigations.

Howto clone existing GIT repository

There are many cases where remote git repositories provide a central location to store files such as source codes. The command 'clone' is used to retrieve a remote git repository. Depends on how a user is provided access to the server, by http, ssh or other methods. 

Here is how its done on Centos Linux, where it defaults to create a folder which is the same name as the repository.

cd /var/www

git clone ssh://nicholas@remoteserver:22/repo/tboxmy.git

Another approach is to create in our empty folder myproject

git clone ssh://nicholas@remoteserver:22/repo/tboxmy.git myproject

How to add user to the remote

git remote add newuser ssh://nicholas@remoteserver:22/repo/tboxmy.git

View remote users

git remote -v 

Adding standby node to Postgresql replication

 Postgresql database version 11 uses write ahead log (WAL) to ensure data integrity. It contains a log of all activity within the database, and helps with recovery of data. WAL records are saved to a WAL file. The insert position is a log sequence number (LSN) that is a byte offset into the logs, increasing with each new record.

Here, I am provided a Postgresql database that have been configured as a MASTER database. A user has been created on the Linux server and a role within the Postgresql called "replication". Now a new linux server have been provided for purpose of running a standby node, this has the same version of Postgresql database as in the MASTER server.

Installation of Standby

For simplicity the IP of MASTER will be 10.1.1.100, and for STANDBY will be 10.1.1.101.

Following information is provided by the MASTER slave;

Linux username: replication

with password: password

Replication standby server is assigned name: replication

Folder of the installation: /var/lib/pgsql/11/data

Within MASTER, the STANDBY server IP has been added to its pg_hba.conf. 

Lets configure the STANBY server

Step 1: Login as postgres user

sudo su postgres

(enter your password)

cd

Step 2: Replace local data with MASTER data

cd 11

mv data data.original

We will use the option -R to generate the default recovery.conf file.

pg_basebackup -h 10.1.1.100 -U replication -D /var/lib/pgsql/11/data -P --password --slot replication -R

(enter password provided by MASTER)

chmod go-rw data

cd data

Step 3: Start Postgresql server

Ensure firewalld allows the database access.

Review contents of recovery.conf, standby_mode should be 'on'.

sudo systemctl start postgresql-11

Monitoring replication process

STANDBY server.

The latest WAL file, is stored in the folder data/pg_wal. Display current process of startup and which WAL file is being pocessed.

ps -eaf|grep startup

postgres  186454  186452  0 17:51 ?        00:00:02 postgres: startup   recovering 000000010000000C00000024

Display the LSN

ps -eaf|grep receiver

postgres  186460  186452  0 17:51 ?        00:00:06 postgres: walreceiver   streaming C/24C1DA10

Login to psql as postgres user,

SELECT pg_last_wal_receive_lsn();

 pg_last_wal_receive_lsn

-------------------------

 C/24D0B740

(1 row)

SELECT pg_last_wal_replay_lsn();

 pg_last_wal_replay_lsn

------------------------

 C/24D15138

(1 row)

MASTER server

Display the replication connection.

ps -eaf|grep postgres

It should display a line that contains

postgres: walsender replication 10.1.1.101(49840) streaming C/24C90AF8

login the psql,

sudo su postgres

psql

SELECT pg_current_wal_lsn();

 pg_current_wal_lsn

--------------------

 C/24D243A0

(1 row)

SELECT * FROM pg_stat_replication;

-[ RECORD 1 ]----+------------------------------

pid              | 2322396

usesysid         | 98654

usename          | replication

application_name | walreceiver

client_addr      | 10.1.1.101

client_hostname  |

client_port      | 49840

backend_start    | 2021-11-24 17:51:54.164144+08

backend_xmin     |

state            | streaming

sent_lsn         | C/24D88B18

write_lsn        | C/24D88B18

flush_lsn        | C/24D88B18

replay_lsn       | C/24D88B18

write_lag        | 00:00:00.000447

flush_lag        | 00:00:00.001416

replay_lag       | 00:00:00.001419

sync_priority    | 0

sync_state       | async

Determine if STANDBY is lagging.

On the STANDBY server, run these in psql.

SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()

THEN 0

ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())

END AS log_delay;

It should result in a value of 0 if everything is in sync.

Display the last last records written.

select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

-[ RECORD 1 ]-----------------+------------------------------

pg_is_in_recovery             | t

pg_is_wal_replay_paused       | f

pg_last_wal_receive_lsn       | C/24DF6580

pg_last_wal_replay_lsn        | C/24DF6580

pg_last_xact_replay_timestamp | 2021-11-24 17:52:29.764273+08

Troubleshooting

Error Canceling statement due to conflict with recovery

This is due to standby server getting updates/deletes in the WAL stream that will invalidate data currently being accessed by a running query. Usually occurs when queries are time consuming.

Solution:

Edit data/postgresql.conf to increase max_standby_streaming_delay, which allows the standby server to intentionally increase replication lag to allow queries to finish. Case where write of archives are taking a longer time than expected, then increase max_standby_archive_delay.

Note: Posting this ahead of time, for comments on understandability of information.