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 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.* --columns in a table \d+ my_table_name --change the schema for a table ALTER TABLE myTable SET SCHEMA myschema;... Read more →