PostgreSQL : Types of Shutdown

There are different ways to make a database shutdown for every database type whether it is MySQL, Oracle, MongoDB, etc, in PostgreSQL as well, there are 3 types of shutdown based on the signal provided

1.    SIGTERM

2.    SIGINT

3.    SIGQUIT

 

We will discuss them one by one

SIGTERM

This type of shutdown is identified as a smart shutdown

After receiving a shutdown signal, SIGTERM 

disallows new connections

let’s existing connection works normally 

shuts down only after sessions are terminated 

If the server is in online backup mode

It additionally waits until the online backup mode is no longer active, new connections still are allowed for superusers only, as they might need to send a request to disable the are online backup mode

If the server is in recovery mode

The recovery process will be stopped only after all regular sessions are terminated.

 

SIGINT

This mode is termed fast shutdown mode

once a SIGINT signal is sent, it

disallows new connections and sends all existing server processes SIGTERM, which makes them abort respective current transactions and exit promptly 

waits until all server process to exit and finally shuts down. 

if the server is in online backup mode

the backup mode will be terminated and the backup will be made useless

SIGQUIT

This mode is termed immediate shutdown mode

The server will send SIGQUIT to all the child processes and wait for them to terminate  and if it is not terminated within 5 sec, it sends SIGKILL

After forcing SIGQUIT,

the database needs recovery on the next startup

only recommended in case of emergency. 

Read More

PostgreSQL : How to check parameter values in postgresql

As part of some basic administration in PostgreSQL databases, we will learn how to check and modify parameters 

there are a few ways to do it

• using the "Show" command
• querying pg_settings catalog
• by directly checking configuration files

 

Assume you need to check the parameter related to the hba_configuration file, like the location of this file but you are not  sure about the parameter name, I just tried with a few options, and it was not giving me proper value, however, it was throwing syntax error like below

 

postgres-# show hba_config;

ERROR:  syntax error at or near "show"

LINE 2: show hba_config;

        ^

postgres=#

 

postgres=# show hba;

ERROR:  unrecognized configuration parameter "hba"

postgres=#

 

for using the "show" command, you must know the exact name of the parameter, or else you can simply use pg_settings catalog to fetch it, I just tried the "like" parameter in pg_settings

 

postgres=# SELECT name, setting, reset_val  FROM pg_settings where name like '%hba%';

   name   |                     setting                     |                    reset_val

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

 hba_file | C:/Program Files/PostgreSQL/10/data/pg_hba.conf | C:/Program Files/PostgreSQL/10/data/pg_hba.conf

(1 row)

 

now you can check using the "show" command

 

postgres=# show hba_file;

                    hba_file

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

 C:/Program Files/PostgreSQL/10/data/pg_hba.conf

(1 row)

 

 

postgres=# show max_connections;

 max_connections

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

 100

(1 row)

 [Also Read - Installation of PostgerSQL]

Read More

PostgreSQL : How to describe table in psql

For the DBA's using a conventional database such as oracle, they have a habit of using DESC or DESCRIBE but in PostgreSQL, it's in different ways, we will discuss the same here

There are 3 ways to describe a table

• using \d
• using \d+
• using view INFORMATION_SCHEMA.COLUMNS

 [Also Read- Basic Administration in PostgreSQL]

suppose you want to see the columns in the pg_roles table from the PostgreSQL database

\d  will details of columns in particular table as shown below 

\d+ is an advanced version of \d, it provides you the definition of the table as well. 

You have another way to describe a table i.e. using SQL query on INFORMATION_SCHEMA.COLUMNS catalog, there are a number of columns in this table, you can limit what you want to see, select * will  give full details, however, I will only select the column name and its data type 

select * from INFORMATION_SCHEMA.COLUMNS;

select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where table_name = 'pg_roles';

Read More

Oracle : ORA-01017, ORA-02063 preceding line from DATABASE_LINK

while creating a database link, I accidentally provided the wrong password of the remote user and while accessing the database link, it got the below error

 

SQL> select * from TECH_AS_OWNER.AS_GARD@asgard_link;

select * from TECH_AS_OWNER.AS_GARD@asgard_link

                                    *

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from ASGARD_LINK

 

Error is quite clear as the password is invalid for a remote account, you have a couple of options to correct it, 

one way is to drop the existing database link and create it again and the other way is to alter the database link, 

 

we will discuss the second option, here i.e., altering the database link.  

to alter the database link, you must have certain privileges as mentioned in the below table 

 

Type of database link	Privilege required
Private	ALTER DATABASE LINK
Public	ALTER PUBLIC DATABASE LINK

 

Below are the commands to alter the database link 

--grant privilege alter database link

SQL> grant alter database link to tech_owner;

 

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER'; 

GRANTEE                        PRIVILEGE

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

TECH_OWNER                     ALTER DATABASE LINK

TECH_OWNER                     CREATE DATABASE LINK

 

SQL> ALTER DATABASE LINK asgard_link

  2  CONNECT TO TECH_AS_OWNER IDENTIFIED BY User#123; 

Database link altered.

 

--verify if database link is working

 

SQL> select sysdate from dual@asgard_link; 

SYSDATE

---------

29-AUG-22 

 

Similarly we can perform activity for public database link

Read More

Oracle : Database Links - Create, Use and Drop

A database link is an important schema object in the oracle database, we should know details about it in order to implement it in real-time applications,

Assume you have two different applications with two different databases, and while generating reports, you need to fetch and merge data from both the database, it’s a good idea to use a database link, i.e. connecting 2 databases together

We will discuss more

Generally, there are 2 types of database links,

• Public 
• Private 

Public - database link, which can be accessed by all users in the databases, 

Private- database link, which can be accessed by only specific users

 The syntax for creating database links is quite similar 

 create [Public/private] database link <link name>

 Explanation with example 

 Assume below details 

	Database	Schema	Connection link
Source details	technodb	tech_owner	technodb
Target details	asgard	tech_as_owner	asgard

 We will be creating database link asgard_link in database technodb, connecting to the asgard database, and will try to fetch data from a remote database

Create a private database link

Connect to the database using userid tech_owner and execute create database link command, before executing the command remember that, you need to have to create database link privileges assigned.

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER';

 

GRANTEE                        PRIVILEGE

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

TECH_OWNER                     CREATE DATABASE LINK

You can see, tech_owner has create database link privileges

Now create a private database link 

SQL> show user

USER is "TECH_OWNER"

SQL> create database link asgard_link

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard';

 

Database link created.

 

Here,

asgard_link – is the database link name

tech_as_owner is a remote user and its password

‘asgard’ – connection string to connect remote database 

Verifying if the database link is working

Execute select query to fetch data from remote database using db link, if returns value, it working fine or in case of error, need to rectify

 SQL> show user

USER is "TECH_OWNER"

SQL> select sysdate from dual@asgard_link;

SYSDATE

---------

29-AUG-22

 

Create a public database link

Similarly, we can create a public database link using the below statements, make sure you have create public database link  privilege granted

SQL> grant create public database link to tech_owner; 

Grant succeeded.

SQL> show user

USER is "TECH_OWNER" 

SQL> create public database link asgard_link_pub

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard'; 

Database link created.

 Now try accessing it through different accounts, you will be able to fetch the data,

SQL> show user

USER is "TECH_OWNER"

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22

SQL> conn /as sysdba

Connected.

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22 

How to drop database link

to drop the private database link, you need to connect using a username account and then execute the drop command,

SQL> show user

USER is "TECH_OWNER"

SQL> drop database link ASGARD_LINK; 

Database link dropped.

 To drop the public database link, you need to mention the public identifier, like below

 SQL> drop public database link ASGARD_LINK_PUB ; 

Database link dropped.

 

Read More

Snowflake : Different editions of snowflake

 As we know, the snowflake is a very hot data warehouse technology today. we will see a different aspect of a snowflake in different articles, here we will discuss its different editions available for use. 

The below image gives an idea about different editions of snowflake

so basically, there are a total of 4 editions of snowflake, in the trail account setup, snowflake won't show you about the fourth edition i.e. Virtual Private, as this is the very highest level of secure edition and is available for paid versions only. 

Standard Edition: This is a basic, introductory edition, standard features are available, not available for large-scale companies, and is enough for small-scale industries and educational institutes

Features (not limited to this) 

 

• automatic data encryption, 
• data warehouse support, 
• time travel up to 1 day 
• disaster recovery up to 7 days
• secure data share, 
• premium support 24/7

 

Enterprise Edition:  This is designed for large-scale industries with all features of the standard level edition 

Features

• All benefits of the standard edition 
• Multi-cluster data warehouse
• time travel- up to 90 days
• materialized views support
• column level security 

[Also read: Snowflake account name]

Business Critical:  with all the features of the enterprise edition, this business-critical edition is designed for the highest data protection and is good for industries with very highly sensitive data such as banking 

 

Features 

• All enterprise features 
• extended support

Virtual Private large: This level of edition comes with the highest level of protection and security
Features 

• All business-critical level features
• dedicated virtual servers 

[Also Read -Snowflake: Introduction and Architecture]

Read More

Snowflake : Introduction and Architecture

Snowflake is a very trending data warehouse technology due to its benefits and features over traditional database technology, here we will have a brief introduction to snowflake data warehouse and its architecture 

Snowflake is true software as a service provided with an advanced data platform.    

snowflake does not use traditional database technology; it is designed with a new SQL engine with innovative architecture designed for the cloud.

snowflake is a completely cloud-based data warehouse technology 

 

Now we will see why snowflake is a true SaaS (Software as Service) 

• There is no hardware to select, install, configure or manage 
• No software to install, configure or manage
• maintenance, upgrade, and patching all is handled by snowflake itself

 

Snowflake cannot be run on an on-premise or hosted server

There are two main parts of the snowflake 

• Compute - to fulfill the need for resources to compute or to perform operations
• Storage - service to store data

  

Snowflake Architecture 

Snowflake architecture consists of three key layers  

1. Storage 
2. Query processing
3. Cloud services 

 

Database Storage 

The database storage layer is basically to store and manage snowflake data, snowflake reorganizes data into internally optimized, compressed, and columnar formats. snowflake stores data in cloud storage such as AWS, GCP, or Azure.

data objects managed and stored by snowflake not are directly visible or accessible by customers of snowflake

 [Also read -How to get account name in snowflake ]

Query Processing (muscle of the system)

The query processing layer performs the execution of queries, this layer consists of virtual warehouses, Virtual warehouse is a virtual compute resource, that can be used to process all of the queries.

each virtual house is independent of the other, so the performance of one virtual warehouse does not impact the other's performance 

 

Cloud Services (the brain of the system) 

This cloud service layer is the combination of different services that are carried out across snowflake. 

Below are the services managed by this cloud service layer

• access and authentication
• infrastructure management
• metadata management
• query parsing and optimization
• security

[Also read -Setting AWS demo account]

Read More

AWS : Setup free demo account

We have discussed the basics of AWS in a previous article, now let's create an amazon web service free demo account

Pre-requisites 

• A valid email id
• A valid payment option such as a credit or debit card

once you are ready, open https://aws.amazon.com in any web browser and click on Create an AWS account

• On the next screen, put the email id and account name.

• On the next screen, amazon asks to verify the email id, and enter the verification code, once you enter the verification code, you need to set a password

• In the next step, enter the below details

• full name
• phone number
• country
• address
• and tick the terms and conditions. 

• On the next page, we need to enter billing information, such as credit/debit card and your PAN is optional here
• On next page, confirm your identity using a phone text message or phone voice call

• On the next page, select your plan

• Once done, you are all set to use amazon web services

'

now you can simply log in to AWS services using aws.amazon.com and click on sign in option and enter the details

Read More