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 →

How to import a csv file with a header into a PostgreSQL table

Question: How can I import a csv file with the first row having the column names? I want to disregard the first line Answer: Use the HEADER parameter. The HEADER parameter according to the documentation "Specifies that the file contains a header line with the names of each column in the file" This is an example of a COPY which copies the data from the csv file into the table. The HEADER parameter will force the COPY action to disregard the fist line on input COPY myschema.myTable(col1,col2,col3) FROM '/tmp/myfile1.csv' DELIMITER ',' CSV HEADER; Read more →

How to migrate MongoDB data to PostgreSQL using mongoexport

MongoDB is a non-relational database and the target database platform is relational - PostgreSQL. Is there a simple approach to migrating data from MongoDB to PostgreSQL? First problem - defining a target schema . Second problem - tweaking the target schema for any changes made by the developers on the MongoDB side aka Evolving Schema. As a one-off migration - probably not such a major problem - but if your basing a DIY ETL process on mongoexport - than it's something you'll need to consider There is a tool provided by MongoDB called mongoexport and it's capable of exporting csv... Read more →

PostgreSQL Foreign Data Wrapper

PostgreSQL implements a feature called Foreign Data Wrapper. The feature supports creating a PostgreSQL foreign table . The foreign table acts as a proxy to a remote data source. It's a similar concept - to SQL Server Linked Server Here is an example of the basic steps to set up up a foreign data wrapper to a MongoDB. The example is for illustrative purposes. -- load extension first time after install CREATE EXTENSION mongo_fdw; -- create server object CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'mymongoserver.net', port '16013'); -- create user mapping CREATE USER MAPPING FOR Postgres SERVER... Read more →

What's new in Postgres 12

Postgres 12 is released and being tested across different organisations. I'm researching these new Postgres features/enhancements Big focus on performance & optimisation Partitioning - Partition tables can now be referenced as foreign keys Btree - Lots of small changes, but big performance improvement , and less storage requirements for multi-column indexes Most-common-value statistics - Allow most-column-value statistics for multiple columns; previously only a single correlation value was recorded for multiple columns.This feature will allow the DBA to create multi column statistics for specific use cases Inline many CTE queries - Overcome optimization barriers Prepared plan control - Prepared statements usually... Read more →

How to find PostgreSQL server messages logs

PostgreSQL supports several methods for logging server messages, including stderr, eventlog,csvlog and syslog. The default is to log to stderr only. This parameter can only be set in the postgresql.conf file or on the server command line. Typical situation - problem arises and you need to debug - if you are not familiar with the PostgresQL logs - you can spend some frustrating time locating the details required for debugging. Use the information to start investigating Step 1 - Identify where the log entries are sent Logon as user to use psql user=# show log_destination; log_destination ----------------- stderr (1 row)... Read more →

Running Highly available Postgres databases in Containers - Cattle and Pets

EDB Postgres Container Platform Webinar Notes I attended a good high-level webinar hosted by EDB Postgres. It was 45 mins long , which is a reasonable time slot. There was the obligatory overview of EDB Postgres, personnel - which is useful if you haven't seen it before. These are some notes on the main points. There was also a demo of setting up Postgres HA within a Kubernetes Orchestration system. >>Best Practises are emerging , and the conversation should focus on Databases and Containers , think of large scalable databases and how does this feed into working with Containers ?... Read more →

How to list extensions in PostgreSQL

Question: I've added an extension on PostgreSQL during the creation of a DBLink extension for cross database queries. The command I've executed is CREATE EXTENSION dblink. How can I list all the extensions within the PostgreSQL db , to confirm the extension is registered? Answer: To view information on PostgreSQL extensions you can either use the psql command or you can use some sql code >>psql method \dx >>sql code method select * FROM pg_extension; >>view available extensions select * FROM pg_available_extensions Read more →

PostgreSQL cheatsheet

--quit from psql \q --version select version(); --Create PostgreSQL database create database MYDB owner myowner; --drop PostgreSQL database.BE CAREFUL USING THIS COMMAND - IT CANNOT BE REVERSED!! TAKE A BACKUP FIRST drop database MYDB; --list PostgreSQL databases \list \l --list schemas in a database with psql \dn --list schemas in database with postgresql select nspname from pg_catalog.pg_namespace; --list tables \dt --list all tables,views,sequences \dtvs the_schema.* --list all servers on Postgres \des+ --list all extensions \dx --create a schema CREATE SCHEMA myschema; --list all schemas in a databases select nspname from pg_catalog.pg_namespace; --list all tables in a particular schema \dt public.*... Read more →