Wednesday, November 24, 2021

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.

Tuesday, November 23, 2021

Howto: Find and kill a hanging query in Postgresql

 In normal case of a query to Postgresql database, it would take a very small amount of time to be processed. However, when query takes a long time to process then it will be a problem as resources such as connection, CPU and RAM will be locked to process that query.

Problem queries may lead to more queries in waiting until the database maximum connection is reached. Default Postgresql is set to maximum 100 connections. This will cause users to fail when trying to login to the application or trying to access the database. These 2 are common examples of when you will need to kill queries.

Lets see break this to 2 parts. 

Part 1, list max connection, and the current settings. Part 2, find the problem query, then to end it.

Part 1

Identify is the maximum connection to database has been reached.

Step 1: list the current maximum connection settings and where it is configured.

Login to postgresql terminal as admin (postgres) and use the commands.

SELECT name, setting, sourcefile FROM pg_settings WHERE name LIKE 'max_connections';



Step 2: List connections used

SELECT count(*) used FROM pg_stat_activity;

Compare values, to determine if this is an issue.

Part 2


Step 1: List query that is currently active

SELECT datid, pid, usename, client_addr, backend_start FROM pg_stat_activity WHERE state = 'active';


Step 2: Kill the query

SELECT pg_cancel_backend(PID);

Where PID is the value identified in Step 1 above.


Part 3

Identify long running queries can be done with a popular query mentioned in (https://wiki.postgresql.org/wiki/Lock_Monitoring)

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start desc;

This concludes how to troubleshoot basic connection and hanging queries.

Monday, November 22, 2021

Add read only user in Postgresql

How to access to Postgresql where only reading of data is required?

Lets create a new user called "reader" and password as "password" who will be given access to the database mydatabase with READ only access. The following sign denotes the sql prompt, which you do not need to type.

=#

The steps listed here are rather generic on PostgreSQL 11 onwards. There are other considerations for security and more complex database designs that you should look into in the future.

Step 1

Connect to the database as administrator.

psql -U postgres -H localhost

=# \c mydatabase


Step 2

Create the new user

=# CREATE USER reader WITH encrypted PASSWORD 'password';

=# GRANT CONNECT ON DATABASE mydatabase TO reader;

=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;

Determine the user exist with the following command

=# \du


Step 3

Verify this by login as that user. Additionally, the user access can be shown through the commands.

=# \l 
=# \l mydatabase

Connect to the selected database and list that user's privileges.

=# \c mydatabase

=# SELECT table_catalog, table_name from information_schema.role_table_grants WHERE grantee = "reader";

=# SELECT table_catalog, table_schema, table_name from information_schema.table_privileges WHERE grantee = "reader";


Managing user privileges

In the case where that user is no longer required, it is only prudent to remove that user from access to the database. It is common to maintain that user within the system, in case they need to assign the user to another database access.

=# REVOKE ALL ON DATABASE mydatabase FROM reader;

Adding user to an existing READ only role would allow faster management of users.

=# CREATE ROLE reader

=# CREATE USER dave IN ROLE reader

Exercise:

Document your steps for this intermediate exercise.

Create a new user with login access name "dave" and password "exercise". 

Create a database name "bookstore" with a table called "users". You are free to assign the field type to a sensible type. 

users(
id, 
name, 
email, 
options, 
created_at, 
updated_at)

Fill this table "users" with 2 rows of data.

Assign user "dave" to be able to connect to this database and read contents of any table in this database.

Connect as user "dave" and verify if the user can only read data, without being able to update its data.


Friday, November 19, 2021

Installing Nodejs and NPM on MS Windows 10

What is NodeJS?

Its what is known as a runtime environment, where programmes written in JavaScript can run.

What is Python?

Python in a programming language. 

What is NPM?

Its a node package manager, which is an application and repository for developing and sharing JavaScript code. Many developers write applications and allow the NPM access to their programmes.

What is Chocolatey?

Its a package manager for windows that runs in Windows PowerShell. Which means, when installing a programme, and there are other dependencies, it will go search and download for you.

How to install Nodejs and NPM?

Download the executable for your version of Windows, at https://nodejs.org/en/download/ 

Example, download and Save the Windows Installaer (.msi) for 64-bit.

Run the installer. Make sure to CHECK box to Automatically install the necessary tools. This will add to path the Node.js and npm programmes.

At end of installation, a Windows prompt will appear. This runs several scripts, press "Space" and follow the instructions which will lead you to the Windows PowerShell prompt.

Test installation

Open a command prompt and type the following;

npm -version

node -v


My output was

8.1.0

v16.13.0

Run Hello World

Lets use an available helloworld package. At the windows terminal;

npm init hello-world helloworld

cd helloworld

npm run start


Use a web browser with the URL that is displayed at the terminal to see the results. In my case, the URL is http://localhost:3000

Issues

The Windows PowerShell waits for a long time with the last message, and task manager show lots of memory+disk activity+Ethernet. 

Created a UnelevatedInstallerTelemetryDecorator

Could it be a slow PC with slow network connectivity? If there are no errors in windows logs, just wait until its done processing and the text appears. My PC took almost 30 mins.

Press Enter to exit.

Thursday, November 18, 2021

Centos 8 insufficient memory for the Java Runtime Environment to continue

On Linux Centos 8, with Apache Tomcat that would start but almost immediately fail. There is 4 Gb of Ram assigned, and without any load, the service kept crashing. It was really strange.

The Java installed was Openjdk version "1.8.0_312", and Apache Tomcat version 9.0.46.

What was the issue?

Each attempt to start Tomcat, it fails then from the logs:

OpenJDK 64-Bit Server VM warning: INFO: os::commit_memory(0x00007f32cd0a7000, 262144, 0) failed; error='Cannot allocate memory' (errno=12)
#
# There is insufficient memory for the Java Runtime Environment to continue.
# Native memory allocation (mmap) failed to map 262144 bytes for committing reserved memory.
# An error report file with more information is saved as:
# /tmp/hs_err_pid49973.log

How can this be? There is 4Gb assigned to the server, with lots of free memory. As can be seen in the configuration, the maximum memory of 1024Mb is assigned to Tomcat. The configuration file is  <TOMCAT>/bin/setenv.sh

export CATALINA_OPTS="-server -Xms1024M -Xmx1024M"

JAVA_OPTS="$JAVA_OPTS -XX:+UnlockExperimentalVMOptions -XX:+UseTLAB -XX:+UseG1GC -XX:G1MaxNewSizePercent=30 -XX:InitiatingHeapOccupancyPercent=40 -XX:G1NewSizePercent=25 -XX:ConcGCThreads=2 -XX:ParallelGCThreads=2 -Dfile.encoding=UTF8 -XX:+PrintGCTimeStamps -XX:+PrintGCDetails -Xloggc:tomcat_gc.log"

export JAVA_OPTS

Here is the result of command free, hardly any memory is in use (less than 80% for sure) when tomcat is NOT running


Why?

Some how Linux was assigning memory usage not in the way I was expecting. Lets check values for memory overcommit with command

sysctl -a |grep overcommit


Or only display how memory is assigned

cat /proc/sys/vm/overcommit_memory


An explanation of this setting

vm.overcommit_memory=0 setting means that the allocated virtual memory is unlimited. Having unlimited virtual memory is important for database servers, which retain extra unused virtual memory allocations for dynamic memory management. Unreferenced allocated memory is not backed by RAM or paging space on Linux systems. 

vm.overcommit_memory=1 setting where the kernel performs no memory overcommit handling. This increases the possibility of memory overload, but improves performance for memory-intensive tasks. 

vm.overcommit_memory=2, setting limits the total amount of virtual memory that can be allocated, which can result in unexpected errors.

How was this resolved?

What worked for me is to set vm.overcommit_memory=1 and ensure Tomcat starts with memory size to use the free space. This is done by updating the variable vm.overcommit_memory to 1, then in tomcat configuration as mentioned earlier, set the CATALINA_OPTS="-server -Xms1024M -Xmx1024M". Once it was working, I gradually increase the memory allocation.

Start with the following command (without reboot). Suggest before applying commands below, to stop any HTTP, PHP-FPM, NGIX and Tomcat services if running.

echo 1 | sudo tee /proc/sys/vm/overcommit_memory
sysctl -p

Restart the Tomcat application and watch Tomcat running smoothly. If all works, then this can be applied permanently by editing the file in /etc/sysctl.d

Now, 

When Tomcat is running with 1024Mb memory assigned.


When Tomcat is running with 2048Mb memory assigned.



These are just overly simplified notes, which might help. Best to still refer to the experts that is managing the server, as each case might be different.

Friday, November 5, 2021

Building Vue Single Page Application (part 2)

 Creating a Vue component. 

This is a continuation of Tutorial from Part 1.

Part 1:

  • This will build a Vue project with a component called Helloworld.
  • Identify basic folder and files
  • Know how to instantiate a Vue application
  • Know the basic flow
  • Know a basic Vue shorthand
  • Be able to create string variable
  • Be able to create custom function
Part 2:
  • Know what is a component
  • Be able to create a new component
  • Be able to create an array variable
  • Know how to display variables in a table format
  • Know what is Bootstrap
  • Be able to add Bootstrap
  • Be able to create a Bootstrap navbar, navbar-brand, navbar-nav and form within navbar


1. Add and test a new component named Products

Create the file src/components/Products.vue and add the contents

<template>
  <div class="products">
      <h1>{{ msg }}</h1>
  </div>
</template>
<script>
export default {
  name: 'Products',

  props: {
    msg: String
  }
}
</script>

Update the App.vue template with data and methods to be used in the component.

Replace <HelloWorld msg="

with 

<Products msg="

Replace import HelloWorld from './components/HelloWorld.vue'

with 

import Products from './components/Products.vue'

Apply changes and run


Add data and for loop

Edit <SCRIPT> content in src/components/Products.vue

export default {
  name: 'Products',
  props: {
    msg: String,
    products: Array,
  }
}

Edit <TEMPLATE> content in src/components/Products.vue

  <div class="products">

      <h1>{{ msg }}</h1>

      <table border=1>

      <thead>

        <tr>

          <th>Name</th>

          <th>Description</th>

          <th>Cost</th>

        </tr>

      </thead>

      <tbody>

        <tr v-for="product in products" :key="product.id">

          <td>{{ product.name }}</td>

          <td>{{ product.description }}</td>

          <td>{{ product.cost }}</td>

        </tr>

        </tbody>

    </table>

  </div>


Edit conponents data in src/App.vue

  data(){
    return {
      message: 'My Products',

      products: [
        {
          id: 1,
          name: "Leather shield",
          description: "Tough leather",
          cost: 120 },
        { id:2,
          name: "Heavy Leather shield",
          description: "Double leather layers",
          cost: 150 },
        ],
    }

Compile and run

2. Add Bootstrap support

Current Vue 3, will cause Bootstrap 5 to be installed.

npm install --save @popperjs/core bootstrap

Edit the file src/main.js to import the installed bootstrap.

import "bootstrap";

import "bootstrap/dist/css/bootstrap.min.css";

3. Create a navigation bar component

Lets use the example code from bootstrap. Create a new file src/components/Navigation.vue

Then add the following code

<template>
<div class="row">
<div class="NavbarPage hold-transition sidebar-mini layout-fixed">
    <nav class="navbar navbar-expand-lg navbar-light bg-light">
        <div class="container-fluid">
            <a class="navbar-brand" href="#">Navbar</a>
            <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
            </button>
            <div class="collapse navbar-collapse" id="navbarSupportedContent">
            <ul class="navbar-nav me-auto mb-2 mb-lg-0">
                <li class="nav-item">
                <a class="nav-link active" aria-current="page" href="#">Home</a>
                </li>
                <li class="nav-item">
                <a class="nav-link" href="#">Link</a>
                </li>
                <li class="nav-item dropdown">
                <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">
                    Dropdown
                </a>
                <ul class="dropdown-menu" aria-labelledby="navbarDropdown">
                    <li><a class="dropdown-item" href="#">Action</a></li>
                    <li><a class="dropdown-item" href="#">Another action</a></li>
                    <li><hr class="dropdown-divider"></li>
                    <li><a class="dropdown-item" href="#">Something else here</a></li>
                </ul>
                </li>
                <li class="nav-item">
                <a class="nav-link disabled" href="#" tabindex="-1" aria-disabled="true">Disabled</a>
                </li>
            </ul>
            <form class="d-flex">
                <input class="form-control me-2" type="search" placeholder="Search" aria-label="Search">
                <button class="btn btn-outline-success" type="submit">Search</button>
            </form>
            </div>
        </div>
        </nav>
</div>
</div>
</template>

<script>
</script>

Next, edit src/App.vue can replave the DIV with ID=app to the following

<div id="app" class="container-fluid">  

  <NavbarRow />


Within the tag <script>, add this new component

import NavbarRow from './components/Navigation.vue'


Add this to the components list

components: {

     Products, NavbarRow,

  },


Compile and run.


Exercise:

1. Create a new component called Footer.vue and like its name, setup within App.vue to display your name and email as the page footer.

2. Describe how each of the modules are loaded through a drawing. 

3. Edit the menu item "Link" in the navigation bar to load a website of your preference.


Building Vue Single Page Application (part 1)

Following is an introduction tutorial to building a Vue application. It works on MS Windows 10 and Centos Linux 7/8.



Part 1:

  • This will build a Vue project with a component called Helloworld.
  • Identify basic folder and files
  • Know how to instantiate a Vue application
  • Know the basic flow
  • Know a basic Vue shorthand
  • Be able to create string variable
  • Be able to create custom function
Part 2:
  • Know what is a component
  • Be able to create a new component
  • Be able to create an array variable
  • Know how to display variables in a table format
  • Know what is Bootstrap
  • Be able to add Bootstrap
  • Be able to create a Bootstrap navbar, navbar-brand, navbar-nav and form within navbar


Pre-requisite: 

Nodejs version 12.x

NPM version 6.14.x


Most of the commands will  be at the command line, 

Install VUE

npm install -g @vue/cli

vue --version
@vue/cli 4.5.15

vue create helloworld

Select Vue 3, using arrow keys.


Run server for VUE

cd helloworld

npm run serve

Test the new site by opening a web browser and pointing to URL http://localhost:8080/

Press Ctrl-c to stop the server


Parts of Vue

Following are basic files and folders of interest;

src/main.js : root Vue instance is declared and configured.

src/App.vue : contains root Vue components. Notice the page title is defined here in the value "msg".

public/index.html : Rendering page of target "app" or should Vue instance fail.

src/components/HelloWorld.vue : Codes for the initial page that root Vue has declared.


This concludes creating a default Vue project. In each of the exercise below, view the results with

npm run serve


Exercise:

1. Update variable msg string

Replace the page welcome message with your own in the file src/App.vue


2. Update string using component data

In Vue, there are short hand notations. One of this is ":msg" which is shorthand for our variable msg in "v-bind:msg".

In the file src/App.vue edit the components

  components: {
    HelloWorld
  },
  data(){
    return {
      message: 'This is my custom title!'
    }
  }

Replace HelloWorld place holder with

<HelloWorld :msg="message"/>


3. User input to display variable in popup window

Edit file src/App.vue, add after the line <HelloWorld .....

<input type="text" v-model="message" />
<button @click="alertMessage">Ok</button>


Add another component methods, for the function alertMessage to be called

  data(){
    return {
      message: 'Hello World!'
    }
  },

  methods: {
    alertMessage(){
      alert(this.message)
    }
  }


Blog Archive