In this tutorial, we will see how to execute .sql file in PostgreSQL. We can execute/run .sql or script file from following methods.
1. From the Linux shell
2. From the psql tool
psql -h hostname -d database_name -U user_name -p 5432 -a -q -f filepath
In this article, we are going to see How to get the PostgreSQL table structure. Table structure is nothing but to get the list of columns, datatypes and constrains information.
PostgreSQL table structure using meta command:
\d+ table_name;
Example:
\d+ dept
Output:
Continue reading How to get the PostgreSQL table structure
1. Query to get list of databases.
\l or select datname from pg_database;
2. How to start, stop and restart PostgreSQL server
systemctl stop postgresql systemctl start postgresql systemctl restart postgresql
In this article, we will see how to find PostgreSQL Server uptime through simple query. uptime is the difference between PostgreSQL start time and current time.
Lets connect to PostgreSQL Server and run the commands:
Get the PostgreSQL Server start time by using below command:
select pg_postmaster_start_time();
pg_postmaster_start_time
——————————
2019-11-16 09:31:03.47614-08
(1 row)
Continue reading How to find postgresql server uptime
In this article, we will see how to install PostgreSQL 11 on Redhat Linux Operating System.
Steps to Install PostgreSQL 11 on Redhat:
Step1: Configure PostgreSQL yum repository:
rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Step2: Install PostgreSQL 11 Server.
Continue reading Install PostgreSQL 11 on Redhat Linux Operating System
It is daily activity to monitor the database size on any database server. We have to check the database size either for planning or space monitoring.
1. Query to check the database size in PostgreSQL:
select datname database_name,pg_database_size(datname) from pg_database where datname='name of the database';
PostgreSQL 11 installation on Mac OS is simple and less time taking.
PostgreSQL 11 installation on Mac OS Steps:
1. Download the PostgreSQL software.
2. Install postgreSQL server.
3. Connect to PostgreSQL server.
These are explained in this video.
In this tutorial, we are going to see process to restart PostgreSQL on CentOS Linux Distribution by two methods.
Restart PostgreSQL by using systemctl command:
systemctl restart postgresql-10
Restart PostgreSQL by using service command:
service postgresql-10 restart
In this tutorial, we are going to Start and Stop Postgresql server on CentOS 7 Linux distribution.
How to stop PostgreSQL Server on Linux:
To get the service name run the below command on Linux:
systemctl list-units|grep postgresql
systemctl stop postgresql-10 or service postgresql-10 stop
To verify the PostgreSQL server status:
systemctl status postgresql-10
How to start PostgreSQL Server on Linux:
systemctl start postgresql-10 or service postgresql-10 start
To verify the PostgreSQL server status:
service postgresql-10 status
</pre
In this article, we are going to find out the top 10 tables in PostgreSQL Database by SQL query.
To get top 10 tables in PostgreSQL Server database:
SELECT table_name ,pg_relation_size(table_schema || '.' || table_name) as size FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;
Output:
table_name | size ------------+------ stock | 8192 orderline | 8192 barcode | 8192 customer | 8192 item | 8192 orderinfo | 0 (6 rows)
To get top 5 tables in PostgreSQL Server database:
SELECT table_name ,pg_relation_size(table_schema || '.' || table_name) as size FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 5;
Output:
table_name | size ------------+------ stock | 8192 orderline | 8192 barcode | 8192 customer | 8192 item | 8192 (5 rows)
