How to use PostgreSQL pg_basebackup

pg_basebackup is a backup utility for ONLINE file system level backups. used to take base backups of a running PostgreSQL database cluster. pg_basebackup waits for the server checkpoint and then start the Postgres data directory copy These are taken without affecting other clients to the database, and can be used both for point-in-time recovery Example pg_basebackup -U mypostgresuser -p 6666 -v -P -F tar -x -D /mybackup_directory/ -l 11_05_2019 U = username p = port v = verbose P = Progress report F = Format of the output D = directory for output l = label For a list of... Read more →

The Amazing Babelfish for PostgreSQL by AWS

Some interesting news in the AWS re:Invent 2020 - Keynote with Andy Jassy . Introduction of the new Babelfish for PostgreSQL , aiming to to offer a new translation capability to run SQL Server apps on Aurora PostgreSQL. One of the main selling points is the capacity to understand SQL Server proprietary dialect t-sql and the Tabular Data Stream (TDS) Protocol Some underlying logic , which has led to the development & introduction of Babelfish for PostgreSQL: 1) Databases are hard to manage which partly has led to the massive uptake on managed RDBMS service. Despite growth in AWS managed... Read more →

How to change PostgreSQL database data directory

Question: The Postgres data_directory is showing a value via the SHOW data_directory command. postgres=# SHOW data_directory; data_directory ------------------------ /var/lib/postgres/data (1 row) How can I change the database directory to another directory? Answer: The steps below are a standard approach. The steps will vary according to specific requirements, and may require a more customised approach Step 1 - Identify current data directory path SHOW data_directory; Step 2- Stop Postgresql services systemctl status systemctl stop Step 3 - create a blank directory on the target path e.g mkdir /mydirectory/data --change ownership to the postgres user , for example chown postgres:postgres... Read more →

How to use auto-increment with PostgreSQL sequence

A developer contacted me to to tell me they were experiencing problems with enabling an auto-increment on a PostgreSQL table.He wanted to know if the privileges assigned to his user credentials had the right level of permission to create a sequence. I reviewed the privileges and identified some missing details: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschema1 TO user1; As a test I logged on as the user's credentials and completed some simple validation tasks to confirm they could complete a CREATE SEQUENCE and add to a table. Step 1 : Create a simple SEQUENCE CREATE SEQUENCE myschema1.jvtest_seq;... Read more →

How to find Default Access Privileges on PostgreSQL with pg_default_acl

This is a basic code sequence to set the Default Access Privileges on a PostgreSQL database. This example defines future privileges for future objects for this user. But what if you want to view the stored information about default access privileges ? There is a view called pg_default_acl ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO myuser; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL PRIVILEGES ON SEQUENCES TO myuser; If you want to view the default access information stored you can use the PG_DEFAULT_ACL view. The default record set i.e SELECT * FROM PG_DEFAULT_ACL will... Read more →

bash: /home/myuser/.ssh/authorized_keys: No such file or directory

Question: When I type in cat ~/.ssh/authorized_keys to a RHEL 7 server I get the following message: cat: /root/.ssh/authorized_keys: No such file or directory I'm going through the process of setting up a SSH to create a connection between BART and client Potgresql servers - to backup Postgresql databases. How can I fix this issue? Answer:You need to create the ssh directory for the first time and create the authorized keys file. Create the .ssh directory. First check if actually already exists mkdir ~/.ssh Set the right permissions for the new .ssh directory. Very important step chmod 700 ~/.ssh Create... Read more →

How to know PostgreSQL is running or installed

Question: How do I know if PostgreSQL is running or installed on a Linux server ? Answer: There are some ways to check whether PostgreSQL is running or installed, but it will depend on how PostgreSQL was installed . It pays to ask the user , how PostgreSQL was installed. Some different methods of PostgreSQL installation & configuration could include: -Installed from rpm packages and started by init script -Installed \ running but the installation is not associated to the default PATH or even the default port -Installed from rpm packages but not set to start As a starter these... Read more →

How to use pgbench for PostgreSQL benchmark testing

Pgbench is used for Postgresql benchmark testing. Pgbench iterates through a bunch of SQL statements . There are various configurables such as sessions (single or multiple) and clients. The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type testing. The measurement is based on transactions per second. In other words - you'll define how many transactions to execute per client and this will result in a rate per second. There is a default test framework – which involves 5 SELECT , UPDATE, INSERT commands per transaction- there are options to customise... Read more →

How to clear Postgresql database cache

Question: In SQL Server there are two commands which clear down the data and query caches - DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. Are there similar commands in PostgreSQL hosted on Linux? I want to clear down datasets from memory cache to allow query performance tuning. Answer: If you'd like to view what is in the buffer cache - pg_buffercache will present to you what is in the cache. Of course , pg_buffercache needs to be installed. It's a complicated data set and requires some knowledge to interpret. Unfortunately - there isn't a straightforward way of clearing down the database cache... Read more →

How to calculate the PostgreSQL database size

Question : How can I calculate the size of a PostgreSQL database using psql?I want to use the command line and execute a sql statement for various checks? Answer: An easy way to derive the Postgresql database size is to use the pg_database_size() function. The pg_database_size() reads the database name and returns the database size in bytes. For example : SELECT pg_database_size('my_db_name') This returns in bytes - which unless you enjoy calculating byte sizes - there is another function you can use which will make these numbers human readable - pg_size_pretty() Combining these two functions will return the database size... Read more →