Found 26 Articles for PostgreSQL

Introduction Navigating the complex world of PostgreSQL system architecture can be challenging, especially when it comes to optimizing your database management. As one of the most advanced relational databases worldwide, understanding its intricacies is crucial for effective data handling. This article provides a comprehensive guide on the inner workings of PostgreSQL's architecture, from client-side processes to potential scalability issues. Ready to decode this essential piece of any modern software infrastructure? Let's dive in! Overview of PostgreSQL System Architecture The PostgreSQL system architecture consists of various components, including client-side processes, the Postmaster daemon process, back-end processes, and shared pool. Client-side Process ... Read More

Introduction PostgreSQL is one of the most popular open-source relational database management systems (RDBMS). It provides a wide range of features such as data integrity, fault-tolerance, and scalability. One of the essential features in PostgreSQL is Streaming Replication. It allows you to create multiple copies of a PostgreSQL database cluster in near-real-time by continuously streaming the changes from the primary node to the standby nodes. Streaming replication works by streaming the write-ahead log (WAL) data generated by the primary node to one or more standby nodes over a network connection. Configuration of Master Node for Streaming ... Read More

Introduction PostgreSQL is a powerful, open-source object-relational database system that is widely used for enterprise-level applications. It boasts extensive features and capabilities, including support for advanced data types, scalability, reliability, and security. With PostgreSQL, users can store vast amounts of structured data with ease. PostgreSQL is one of the most popular database management systems available today. It was first released in 1989 as an open-source project and has since become the go-to choice for many organizations worldwide due to its robustness, flexibility, and scalability. Being an open-source project means that it is free to use and can be modified by ... Read More

Introduction PostgreSQL is an open-source relational database management system (RDBMS) known for its robustness, stability, and ability to handle complex and large data sets. On the other hand, Kubernetes is an open-source container orchestration system that automates the deployment, scaling, and management of containerized applications. Combining these two powerful technologies provides a highly scalable and reliable environment for hosting your PostgreSQL database. In this article, we will explore how to deploy PostgreSQL on Kubernetes. We will start by explaining what PostgreSQL and Kubernetes are and the benefits of deploying PostgreSQL on Kubernetes. Then we will go through an overview of ... Read More

We can remove Unicode from Jenkins Console Output logs. To perform this, we should create a Collection with at least a single request. Along with it, we have to install Newman.Step 1 − Click on the arrow appearing to the right of the name of the Collection. After that, click on Share.Step 2 − SHARE COLLECTION pop-up shall open. Navigate to the Get public link tab and copy the link which is pointed out in the below image.Please note − The link obtained is specific to a particular user.Step 3 − Launch Jenkins and go to the Jenkins Job which ... Read More

In this post, we will understand the difference between MySQL and PostgreSQL.MySQLIt is a relational database management system.It is the product developed by Oracle Corporation.It is supported by Windows, Mac OS X, Linux, BSD, UNIX, z/OS, Symbian, AmigaOS.It can’t be extended.In this system, the phpMyAdmin tool gives the GUI.Mysqldump and XtraBackup provide backup in MySQL.It provides temporary table.It doesn’t provide a materialized view.It doesn’t provide Data Domain Object to the system.PostgreSQLIt is an object-relational database management system.It was developed by the Global Development Group.It is supported by Windows, Mac OS X, Linux and BSD but not by UNIX, z/OS, Symbian, ... Read More

Although quite infrequent, you may come across situations wherein you need to define the primary key on an existing table. This can be achieved using the ALTER TABLE statement.The syntax is −ALTER TABLE table_name ADD PRIMARY KEY (column_name1, column_name2, …., columns_nameN)As can be seen from the above syntax, you can define PRIMARY KEY on multiple columns. When you have defined the PRIMARY KEY on multiple columns, the condition is that the column pairs should have unique and non-null values. Thus, if the PRIMARY KEY is defined on (column1, column2), the values (value1, value2), (value3, value2), and (value1, value4) are allowed. ... Read More

Let us create a new table containing a single timestamp column −CREATE TABLE timestamp_test(    ts timestamp );Now let us populate it with some data −INSERT INTO timestamp_test(ts) VALUES(current_timestamp), (current_timestamp+interval '5 days'), (current_timestamp-interval '18 hours'), (current_timestamp+interval '1 year'), (current_timestamp+interval '3 minutes'), (current_timestamp-interval '6 years');If you query the table (SELECT * from timestamp_test), you will see the following output −ts2021-01-30 19:23:24.0080872021-02-04 19:23:24.0080872021-01-30 01:23:24.0080872022-01-30 19:23:24.0080872021-01-30 19:26:24.0080872015-01-30 19:23:24.008087Now, in order to extract hour, minute, etc. from the timestamp column, we use the EXTRACT function. Some examples are shown below −SELECT EXTRACT(HOUR from ts) as hour from timestamp_testOutput −hour19191191919Similarly −SELECT EXTRACT(MONTH from ts) as ... Read More

Often, we have some very long table names, and writing the table name every time is troublesome. We can use aliasing to help us there, thanks to which, we will need to write the long table name only once.The table aliases are generally written in the FROM part of the statement, or the JOIN part.For example, consider that we have two tables, marks, and student_info, defined respectively below −marksnameroll_noperc_marksAniket1224Siddhi4565Yash2642Isha5687student_infonameroll_noagegenderAniket1226MIsha5625FSiddhi4523FYash2625MNow, if you want to see the name, roll_no, perc_marks, and age of the student in one query, your query will look like this −SELECT marks.name, marks.roll_no, marks.perc_marks, student_info.age FROM marks LEFT ... Read More

Suppose you have a table user_info that contains the state and district of different users. An example is given below −namedistrictstateAnilMumbaiMaharashtraJoyJhalawarRajasthanRonPuneMaharashtraReenaMeerutUttar PradeshNow, if you want to combine the state and district in a single field called location, this is how you should be able to do it −SELECT name, district || ', ' || state as location from user_infoThe || operator is the string concatenation operator. The output will be −namelocationAnilMumbai, MaharashtraJoyJhalawar, RajasthanRonPune, MaharashtraReenaMeerut, Uttar PradeshSimilar operations can also be performed on numerical values. Suppose you have a table marks containing the total marks scored by students and the maximum ... Read More