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 →

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 →