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 →

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 →