-
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…
-
PostgreSQL: Upgrade a User to be a Superuser?
In this article we are going to cover how to upgrade a user to be a superuser in PostgreSQL. In PostgreSQL there is a unique term “role”, the term “role” will be discussed later because it’s a complicated feature by itself. TL;DR: ALTER USER username WITH SUPERUSER; Upgrade a User to be a Superuser In…
-
Run a PostgreSQL .sql File Using Command Line Arguments
psql is capable of running SQL statements written into a .sql file. Either, you want to run it on a local or remote server you can use psql client. TL;DR: psql -f filename Requirement Either for new product installation, migration, cloning environment, we may need to run sql scripts written in files. In my professional…
-
Insert, on Duplicate Update in PostgreSQL?
You have a value to insert to database, but you are not sure, if this row exists on database. What will you do? Upsert is the right term for you, UPdate, if fails inSERT. We can generalize it as insert, on duplicate update. TL;DR: INSERT INTO table () VALUES () ON CONFLICT (row) DO UPDATE…
-
How to Do an Update With Join in PostgreSQL?
When you need to find the row you want to update with a join statement, you can use join statement as same as select statements in PostgreSQL. TL;DR: UPDATE orders AS v SET price = s.price_per_product FROM products AS s WHERE v.product_id = s.id; In update statements you can use from and join statement as…
-
PostgreSQL DB Size Command
Capacity planning is an important topic of database management, in terms of size, performance and robustness. TL;DR: \l+ database_name Purpose: PostgreSQL DB Size Command Database size is an important metric for database administrators, that shows growth ratio of the total data. The amount of data we have in our databases effects database performance. Bigger data…