In this article, we will see How to find the error log location in PostgreSQL on Windows or Linux environments.
We can get the error log location by running the command log_directory command in psql shell. Continue reading How to find the error log location in PostgreSQL on Windows or Linux environments
In this article, we will see how to set PostgreSQL environmental variables permanently in Ubuntu Linux step by step.
Continue reading How to set PostgreSQL environmental variables permanently in Ubuntu Linux
In this article, we find the solution for Fatal error.
FATAL: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off
Reason: The database name has to be replication as all does not cover replication connections.
Add below lines in pg_hba.conf file.
host replication postgres 127.0.0.1/0 trust local replication postgres peer
Dont forgot to reload. Run either of below commands to reload.
pt_ctl reload() systemctl reload postgresql
In this article, we will see the solution for error “FATAL: password authentication failed for user “postgres””.
1. Reason is the user postgres has no DB password set on Ubuntu by default. That means, that you can login to that account only by using the postgres OS user account.
Continue reading FATAL: password authentication failed for user “postgres”
In this article, we will see how to install PostgreSQL 13 on Redhat.
PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
1. Install the repository
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2. Disable the built-in PostgreSQL module:
dnf -qy module disable postgresql
3. Install PostgreSQL 13 version using below command.
dnf install -y postgresql13-server
4. Now initialize database b running below command.
/usr/pgsql-13/bin/postgresql-13-setup initdb
5. Enable the PostgreSQL service.
systemctl enable postgresql-13
6. Start the PostgreSQL Server on Redhat or CentOS
systemctl start postgresql-13
7. Check the status PostgreSQL on Regdhat or CentOS
systemctl status postgresql-13
1. Switch to postgres user.
sudo su - postgres
2. Then connect to the PostgreSQL server using psql tool.
psql
3. Now change the password for user “postgres”
alter user postgres with password 'admin@123';
So, in this article, we have explained How to install PostgreSQL 13 on Redhat step by step.
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
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';
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)
