SQL DBA Blog

Category: Postgres

Postgres Blog Archives, by Peter Whyte (SQL Server Database Admin). Includes Postgres posts from a SQL Server DBA’s perspective!

Introducing DBASco: SQL Server Database Admin Blog
Greetings Tech People!
If you’ve been following my technical journey on peter-whyte.com, I’m excited to share a new platform that dives into the world of Database Reliability Engineering and beyond – welcome to DBASco.com!
At DBASco.com, I share my latest technical writings, focusing on a spectrum of topics catering to IT enthusiasts, database/system administrators, and those passionate about the software behind reliability engineering. The primary categories align with the ones you’re familiar with at peter-whyte.com, ensuring a seamless transition for any dedicated readers.
This site here is now my secondary tech blog site. If you’ve been on here before you will have seen a lot of display ads, which are disabled for the time being. I was using Ezioc, and I was earning around $50-60 per month, until I neglected things for too long during 2023 and there was a drop in earnings. The site certificate had issues during 2023 Q4, and we are back fully operational as of this week.
My enjoyment is the technical writing, refining, and seeing where tests lead me, exploring my passion for the things I do as a career and on the side. I hope you enjoy what I write, and it serves a purpose if visiting via Online Search to understand a concept or resolve an issue.
DBASco Blog Categories
DBASco: SQL Server: Explore Microsoft SQL Server with in-depth insights into queries, optimization techniques, and best practices. Uncover the holistic experience, including the realm of Windows Administration that intertwines seamlessly with the life of a SQL Server DBA.
DBASco: PowerShell: Dive into the enchanting world of PowerShell scripting with hands-on guides, expert tips, and powerful tricks. Unearth the magic of automation as you streamline your workflow with carefully crafted scripts and comprehensive guides.
DBASco: WSL and Linux: Journey into the world of Windows Subsystem for Linux (WSL) and Linux, discovering the synergy between these platforms and unraveling the potential for seamless integration. Benefit from real-world experiences and extensive testing, providing you with valuable insights into cross-OS platform development.
DBASco: Other: From RDMS’s like MySQL, Postgres, Redshift to semi-related technologies like AWS, and even technical guides on WordPress and website management – expect a wealth of knowledge that keeps you at the forefront of the ever-evolving tech landscape.
DBASco Latest Articles
RedShift: Script to Show Running Queries
Published on January 24, 2024
Uncover the power of Amazon Redshift, a cloud-based data warehousing giant. Learn how to efficiently manage and analyze extensive datasets with a detailed script to reveal running queries.
RedShift: Creating a User and Promoting to Superuser
Published on January 24, 2024
Unlock the secrets of Amazon RedShift as we guide you through creating a user and elevating their privileges to superuser status. Essential insights for effective cluster management.
PowerShell Script: List Files with Sizes and Dates
Published on January 23, 2024
Master a practical PowerShell script that simplifies listing files in a directory. Dive into efficient file management with sizes and dates at your fingertips.
Backing Up a Database in SQL Server Using Command
Published on January 22, 2024
Ensure the safety and integrity of your data with a comprehensive guide on backing up a database in SQL Server using command-line tools. A crucial aspect of database management unveiled.
SQL Server Script: Get Last Backup Dates
Published on January 22, 2024
Stay informed about the last backup times, a critical element in maintaining data integrity. Enhance your SQL Server skills with a script that simplifies tracking backup dates.
PowerShell: Counting Files, Words and Rows
Published on January 22, 2024
Embark on a PowerShell journey with a script that counts files, words, and rows. Discover the power of PowerShell for efficient data processing and analysis.
Adding a Filegroup in SQL Server
Published on January 21, 2024
Learn the process of adding a new filegroup to a database in SQL Server. Elevate your database architecture skills with a step-by-step guide.
January 25, 2024
Why Use WHERE 1=2 in SQL
Some time ago I wrote a blog post on why use WHERE 1=1 in SQL. This time it’s why use WHERE 1=2, but really this can be WHERE 1=9 or anything that isn’t a 1, we just want the false statement.
WHERE 1=1 is TRUE; has no difference on execution times and can be used for ease of adding/removing comments & commas on queries that you are building.
WHERE 1=2 is FLASE; is usually used for building the structure of a table by copying it from another, without copying any of the rows. It does not copy keys & constraints.
The following example SQL should help explain this, and the SQL Syntax used below can be run on any of the SQL Systems tagged in this post (MySQL, MS SQL, Redshift, Postgres & more).
The syntax will work on other SQL systems, however, the table names used in the SQL below will not be available on all platforms. Please amend SQL table names as necessary.
```
-- Create table copy from another table
CREATE TABLE infoschema_tables AS (SELECT * FROM REMOVEinformation_schema.tables WHERE 1=1);

-- Count rows of created table 
-- (not required in this psql terminal example as it shows row counts)
SELECT COUNT(*) FROM infoschema_tables;

-- Drop table
DROP TABLE infoschema_tables;

-- Create table copy from another table but do not copy rows
CREATE TABLE infoschema_tables AS (SELECT * FROM REMOVEinformation_schema.tables WHERE 1=2);

-- Count rows of created table
SELECT COUNT(*) FROM infoschema_tables;

-- Clean-up drop table
DROP TABLE infoschema_tables;
```
May 5, 2022
How to Install Postgres on Ubuntu 20.04
This post is a short note on how to install Postgres on Ubuntu 20.04.
I’m doing this in WSL, however this shouldn’t make any difference if you’re on the same Ubuntu version.
First, lets update our local packages by running the following apt command –
```
# update local packages
sudo apt update
```
This Postgres installation is very quick and simple.
The following command is also including the ‘-contrib‘ package which gives us extra functionalities.
```
# install postgres on ubuntu
sudo apt install postgresql postgresql-contrib
```
Once the installation completes, we can enter psql using the postgres user created automatically during the install.
```
# login to psql with the out of box postgres user
sudo -u postgres psql

\q (to quit back to terminal)
```
There may be a need to restart the services post install which can be done by running the following.
```
# restart postgres service ubuntu
sudo service postgresql restart
```
May 2, 2022
List all Schemas, Tables & Columns in Redshift or Postgres
This post contains a SQL script that will return all schemas, tables and columns in Redshift or Postgres.
I’m combining the Redshift and Postgres database blog categories here. Redshift was forked from Postgres and is similar in a lot of ways.
One of the feature differences between Redshift and Postgres is that Redshift has is column-level access control. The script in this post is useful if you want to review columns that contain sensitive data (PII) before making changes to user and role permissions.
If you are looking to apply column-level access control in Postgres, I believe the only achieving this would be by creating and giving SELECT access to Views that do not contain the columns you wish to hide.
Script to Show all Schemas, Tables & Columns
The SQL below will return all schemas, tables, & columns within RedShift or Postgres.
We can add a filter to the above statement (as per comment line) to filter out system schemas. Umcomment this line if you only want to return objects that have been created by users.
September 27, 2020
Why use WHERE 1=1 in SQL Queries
A common SQL question is, why would anyone use WHERE 1=1 in their queries? And what does it do?
The WHERE 1=1 condition means WHERE TRUE. It returns the same query result as it would without the WHERE Clause. There is no impact on query execution time.
This is something you can add to a SQL query for convenience for adding & replacing conditions on the statement. If you have WHERE 1=1 on a SQL query you are writing, all conditions thereafter will contain AND, so it’s easier when commenting out conditions on exploratory SQL queries.
Example: WHERE 1 Equals 1
```
/* where 1=1 example */
SELECT *
FROM TABLE
WHERE 1=1
--  and column1='blah'
    and column2='more_blah'
--  and column3 is not null
```
You can see this would be easier for commenting out WHERE conditions in the SQL ^
Example (Extra): Commenting Columns
This is similar to another SQL querying technique where you have commas before column names, rather than after the column name.
Again, this might work out well for you when commenting out columns on a work-in-progress SQL query.
```
SELECT
     Column1
--  ,Column2
    ,Column3
    ,Column4
--  ,Column5
FROM TABLE
```
Personally, I often make use of both of the above techniques when I write SQL queries.
Convenience is everything when working behind a computer. We have to keep exploring new short keys and any general ways of working to optimize our efficiency. Some things you might not like and won’t adopt, and sometimes GUI is better than CLI. It’s all personal preference, in the end, choose what you are most optimal with.
If you wonder what would happen if we change the WHERE 1=1 to WHERE 1=2, have a look at my other blog post – Why Use WHERE 1=2 in SQL
September 21, 2020
Creating a Linked Server with a Postgres Database
In my previous post, I created a Link Server to another SQL Server instance This time, it’s a link to a Postgres database!
This guide can be followed from start to finish using the links below.
# Install SQL Server (separate post).
# Install PostgreSQL (separate post).
# Test Connectivity to Remote Server (previous Linked Server post).
# Create Linked Server Login on Postgres.
# Install the Postgres ODBC Driver.
# Configure ODBC Data Source Settings.
# Create Linked Server to a Postgres Database.
Create Linked Server Login on Postgres
As always, this kind of task can be done using a management tool like pgAdmin 4 or via command (psql in this case).
Making this one a Superuser for quickness.
And for the post, here’s the above being created using psql.
Install Postgres ODBC Driver
The ODBC driver must be installed on both ends – This is a local set-up though, so I only need to install this once. You can use Stack Builder to install it, or download the package on its own (link here) which I’m doing below.
Click famous Next button.
Accept License stuff.
Specify directory & we don’t need the documentation.
And then install!
Configure ODBC Data Source Settings
We need to point the ODBC driver on the SQL Server to the Postgres database.
Run ODBC Data Sources as Administrator.
Click into the System DSN tab and click Add.
Select the PostgreSQL Unicode driver.
Enter all details required as shown. I created a ‘sales’ database for this test which I’m connecting to.
Always worth it to test.
Create Linked Server to a Postgres Database
(Within SQL Server Management Studio’s Object Explorer)
Expand Server Objects, right-click Linked Servers and select New Linked Server.
The Linked server name at the top of the next window is what you’ll need to include in queries, so choose that wisely.
Then choose a provider for ODBC, enter any semi-relevant Product name, and then enter the Data Source name which you entered within the ODBC driver settings.
Enter the Postgres login details as created above within this post.
I’m ignoring the Server Options tab for now and rolling with the defaults.
When ready, take a breath, then hit OK!
Nothing will display if it’s successful, so have a go at testing the connection.
We’re now linked with the Sales database that’s in Postgres!
March 10, 2019
Reset Default PostgreSQL User Password
A common scenario working with Postgres is when you have forgotten your default Postgres user password. This is the password you will have entered during your Postgres installation.
This is a guide on how to reset the default Postgres user password. This should hopefully help you resolve this issue on your test Postgres environment.
Resetting the local default postgres user password includes the following steps:
– Open the Postgres pg_hba config file.
– Amend the authentication method to allow local connectivity.
– Update Postgres User password.
– Revert the change in pg_hba.config file once verified.
How to Reset Postgres User Password
1. Open your pg_hba.conf file which can be found in your data directory.
Default Path: C:\Program Files\PostgreSQL\10\Data
2. Change the method within the pg_hba.conf file as highlighted below, from md5 to trust.
3. Restart the postgresql service.
4. Open psql in your terminal, connect and execute the ALTER USER SQL Statement as shown below.
5. Now open pgAdmin, you should be able to log back in with the ‘postgres’ user.
May 15, 2018
How To Install PostgreSQL on Windows
PostgreSQL is a free open-source RDMS that is widely used and forked. Other than PostgreSQL itself, Redshift is the only other forked database I’ve ever touched.
This blog post is a very simple wizard run-through of installing PostgreSQL and Stack Builder on Windows for testing.
# Install PostgreSQL on Windows
# Install Stack Builder on Windows
Install PostgreSQL on Windows
PostgreSQL official downloads can be found here. Also see this link (chapter 16) for installing this on a Linux system.
1. Run set-up exe file.
2. Specify installation directory.
3. Select features.
4. Specify data directory.
5. Enter the superuser (postgres) password.
6. Enter the listening port.
7. Default local for English.
8. Next, next, next & you’re done.
Install Stack Builder Components on Windows
Stack Builder is a quick way of getting additional software installed on your server, such as drivers, apache and EnterpriseDB tools.
1. Select your Postgres Sever.
2. Make your selections – pretty handy having things like the drivers in here.
3. Continue on.
4. A reboot may be required, depending on what has been selected.
And we’re done. Next, install a management tool such as PG Admin.
May 14, 2018