-
PostgreSQL Features: The Ultimate Guide
PostgreSQL is a powerful, open-source relational database management system (RDBMS) that is used by a wide variety of organizations, from small businesses to large enterprises. It is known for its scalability, flexibility, and reliability. One of the things that makes PostgreSQL so popular is its wide range of features. Here is an ultimate guide to…
-
PostgreSQL for Database Administrators
PostgreSQL is a powerful, open-source relational database management system (RDBMS) that is used by a wide variety of organizations, from small businesses to large enterprises. It is known for its scalability, flexibility, and reliability. Database administrators (DBAs) are responsible for the day-to-day management of PostgreSQL databases. This includes tasks such as creating and maintaining databases,…
-
Use Database Command in PostgreSQL
Hello! Today we are going to talk about what is equivalent of use database command in PostgreSQL. Use database_name command mostly, refers to changing database connection in most of the database engines. For instance you are working on HR database and need to change your connection to MRP database to update some settings on MRP…
-
How to Create a Read Only User in PostgreSQL
In this article we are going to discuss, how to create read only user in PostgreSQL. Reporting users, or interns, or read only applications need a read only user. A read only user is: But not able to: Read Only User and Role User and role is same thing in PostgreSQL, user has a login…
-
Generating a UUID in Postgres for Insert statement?
UUID stands for universally unique identifier. Unlike auto generated ID’s it is long and includes both numeric and alphanumeric characters. TL;DR: gen_random_uuid(); Randomness of a UUID is not based on an authority, also there is a possibility to be duplicated, in practice, that provides a unique identifier to objects in database. UUID in PostgreSQL PostgreSQL…
-
What is the Format for the PostgreSQL Connection String
Connection string is term PostgreSQL drivers use to connect specified database. In this article we will try to answer question What is the Format for the PostgreSQL Connection String? In general, database connection string in PostgreSQL is: As a note, that is not a JDBC connection string. For JDBC there is a different format, we…
-
Copying PostgreSQL Database to Another Server
Even it is not a day to day operation for a database administrator database copying is one of the mandatory skills. In this article we will talk about copying PostgreSQL database to another server. TL;DR pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname Dumping SQL pg_dump utility is included…
-
How to Specify Password to psql Non-Interactively?
psql can be used as a remote scripting tool for PostgreSQL. While running a script remotely you may need to specify a user’s password. Normally, psql asks for a user’s password interactively. TL;DR export PGPASSWORD=password How psql works? If you specify a remote server that is not same with server you run psql, psql asks…
-
Import SQL Dump Into PostgreSQL Database
Data migrations, or creating a lower level test environment with same data has a requirement, exporting and importing a SQL dump into PostgreSQL database. Today we will cover importing SQL dump into PostgreSQL database. TL;DR pg_restore –dbname=database_name /tmp/path/to/dump.sql Importing SQL Dump Either you need to restore data or you want to create a lower level…
-
How to Reset PostgreSQL Key Sequence
After some administrative operations you might need to Reset key sequence in PostgreSQL. During modernization journey, team PostgreSQL designed shortcuts and improved reset key sequence workflow. TL;DR: ALTER SEQUENCE sequence_name RESET Resetting Key Sequence in PostgreSQL Sequence is the main object that keeps up with row identifiers. When you create a table with a sequence…