How to get PostgreSQL uptime

There is not direct command to get the server uptime. We can get it by the difference of PostgreSQL server start time and current timestamp of PostgreSQL database.

Run the following query in psql or pgadmin tool.

select date_trunc('second',current_timestamp-pg_postmaster_start_time()) as uptime; Output uptime ---------- 19:02:47 (1 row)

Create tablespace in PostgreSQL

What is tablespace?

Tablespace is file system to store databases and its objects. To create tablespace, user must have CREATE USER privilege. Before creating tablespace, we must first create a physical disk location to which to map the tablespace.

Why tablespaces?
Tablespaces makes it much easier for administrators to control how PostrgeSQL’s data table are stored in the file system, which is useful for tasks such as managing large tables and improving performance by distributing the load across different disk drives.

Create Tablespace:

How to quit from Postgresql Command shell psql

Unfortunately we can not come out of PostgreSQL command shell using “quit” or “exit”. But, we can quit using \q or \quit commands.

1. Connect to psql command line utility.

2. Type the psql command as shown below to connect to the psql and enter the password.

postgres@ubuntu:/etc/postgresql/9.5/main$ psql Password: psql (9.5.12) Type "help" for help. postgres=#

Enable Access for remote users in PostgreSQL

By default, remote access is disabled for security measure. We can enable this by editing posgresql.conf and pg_hba.conf files.

Steps: Enable Access for remote users in PostgreSQL

1. Go to Configation files(posgresql.conf and pg_hba.conf) location:/etc/postgresql/9.5/main

How to execute script file in Postgresql

In this article we are going to learn about executing the script file in PostgreSQL database.

Step1: Create file script file using cat command. cat > loginlist.dat and enter the following

How to configure auto locking screen in Ubuntu

If you step away from your Ubuntu desktop for a short while it will automatically lock the screen to prevent others from accessing your files or programs. This feature is included for security reasons.

Auto locking is security feature. The lock screen prevents unauthorized access. It is useful in public settings. But when you’re alone and no one around to access your computer, it’s probably not necessary.

Steps: How to configure auto locking screen in Ubuntu

1. Select System settings as shown in the below.

How to configure auto locking screen in Ubuntu

Find locks in MySQL

In MySQL sometimes one transaction blocks another. The tables that contain information about InnoDB transactions and data locks enable you to determine which transaction is waiting for another, and which resource is being requested. Table used to check locks is information_schema.

We can get locks information using following two methods:

Method1: Using the SHOW FULL PROCESSLIST;

SHOW FULL PROCESSLIST;

ERROR 1040 (HY000): Too many connections MySQL

Following error occurs when connection limit reaches the maximum limit as defined in the configuration file. The variables holding this value is max_connections. If you want connect MySQL server either kill some of existing connections or login as root and then increase the max_connections limits.

ERROR 1040 (HY000): Too many connections

To check the current value of this variable, login as root user and run the following command:

show variables like “max_connections”;

mysql> show variables like “max_connections”;

Output

+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+

Solution for ERROR 1040 (HY000): Too many connections

Show running processes in MySQL

We can get the current running processes in MySQL using ‘show processlist’. This will give us the username, connected database, CPU, query running by user.

show processlist;

mysql> show processlist; +----+---------+-----------+---------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+-----------+---------+---------+------+----------+------------------+ | 26 | root | localhost | sakila | Query | 0 | starting | show processlist | | 29 | mgeorge | localhost | company | Sleep | 2 | | NULL | +----+---------+-----------+---------+---------+------+----------+------------------+ 2 rows in set (0.00 sec)

Powered by k2schools