In this post, we feature a comprehensive PostgreSQL Database Tutorial. PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, and to allow for retrieval at the request of other software applications. It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.
PostgreSQL is developed by the PostgreSQL Global Development Group, a diverse group of many companies and individual contributors. It is free and open-source software, released under the terms of the PostgreSQL License, a permissive free-software license. (Source: Wikipedia https://en.wikipedia.org/wiki/
In this course, we provide a compilation of PostgreSQL tutorials that will help you set up and run your own database management system. We cover a wide range of topics, from installation and configuration, to custom commands and datatypes. With our straightforward tutorials, you will be able to get your own projects up and running in minimum time.
About the Author
Gabriel’s areas of expertise and interest are Linux system administration, shell scripting, database administration (SQL Server, MySQL, Oracle 11g), object-oriented and procedural programming (Python and PHP), desktop applications (C#, Visual Basic, Excel with VBA) and web development (jQuery, HTML5, CSS3, PHP).
He has also been working as a Level-1 TSR (Technical Support Representative) supporting onsite the startup and ongoing operation of the WMS in a major multinational company, running Red Hat Enterprise Linux and Oracle 11g as RDBMS.
With all the Relational Database Management Systems (RDBMs) out there, it may be somewhat difficult to identify the best solution for your needs and to take an informed decision as to which one to choose. Thus, in this series we will provide an introduction to PostgreSQL and share some of the reasons why you may want to consider this solution when exploring the available technologies for a database implementation.
In our previous post (PostgreSQL: Introduction and installation) we explained how to install and use a desktop and a web-based client to query a sample database we created and populated. We also introduced two basic commands to connect to a database (
\c followed by the database name) and to quit (
\q) the PostgreSQL prompt.
In the previous tutorials of this series (“PostgreSQL: Introduction and installation” and “PostgreSQL commands and datatypes”) we explained how to load a sample database (World_db) into our PostgreSQL server and how to get help with both SQL and psql commands. In real-world scenarios, you will need to perform CRUD (Create, Read,Update, and Delete) operations on database tables all the time.
In our previous article we discussed how to free up disk space by vacuuming tables with frequent updates and deletes. Under the hood, this procedure also helps to improve the performance of other CRUD operations performed on those tables. In this tutorial we will explain how to optimize SELECT queries with WHERE clauses using indexes in PostgreSQL tables.
In the first tutorial of this series (PostgreSQL: Introduction and installation tutorial) we explained how to download a sample database and import it into our PostgreSQL server. This approach, although appropriate at the time, did not show how to create a database of our own from scratch – which is an essential skill that every database administrator (DBA) or developer who intends to use PostgreSQL must has.
In our previous article we explained how to use JOINs to create more advanced SELECT queries. However, there are instances when using this technique to retrieve data from two or more tables does not satisfy our requirements or makes the query difficult to read – for example, if we need several JOINs or a subquery to return the desired information.
In the previous articles of this series, we have learned several PostgreSQL database management skills. So far, those skills have only included working with one database on one machine only. Today we will explain how to set up database replication, with a master machine and a slave one, in order to provide redundancy.
Since hardware can fail and human error may occur, having frequent backups and knowing how to restore them are important skills that every system administrator should have. Additionally, you will need to know how to migrate PostgreSQL databases from one machine to another in case you purchase new, more powerful servers.
After we have learned how to set up and configure PostgreSQL for a variety of scenarios, having a database and populating it with data will not be of any use until we can retrieve it and use it in some way. Today, using a mobile-friendly web application is the most common way to accomplish this goal.