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)
We can get the list of databases in PostgreSQL by using the met-command \l or by querying on catalog table ‘pg_database’. There is no show databases in PostgreSQL(psql)
Get the list of databases by using the meta-command \l:
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+---------------------- - lonprod1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres
Get the list of databases by using the catalog table pg_database:
postgres=# select datname from pg_database; datname ----------- postgres lonprod1 template1 template0 (4 rows)
We can get the list of tables by using the meta-command \dt or by using SQL statement.
Get the list of tables in current schema of PostgreSQL by using \dt:
Get the list of tables in PostgreSQL by using SQL statement:
Continue reading How to get list of tables in PostgreSQL Database
Reason: It means that PostgreSQL server is trying to authenticate a user using the Ident protocol, and can’t.
Solution: We have to change the ident to password in the pg_hba.conf file.
Step1: Get the configuration file running the command show hba_file;
Continue reading psql FATAL Ident authentication failed for user