#psql connect to database

[dbajamey]

The Best Ways to Connect to a PostgreSQL Database

If you're working with PostgreSQL, efficient connection methods are key to managing your databases effectively. In this article, you’ll find step-by-step guidance on connecting to PostgreSQL databases using three popular tools:

psql: A command-line utility perfect for those who enjoy executing commands directly.

pgAdmin: A GUI tool offering a user-friendly interface for managing databases.

dbForge Studio: A comprehensive PostgreSQL IDE combining a sleek graphical interface with advanced database management features.

The article explains how to connect to both local and remote PostgreSQL databases and highlights common connection errors like “authentication failed” or “connection refused,” providing actionable fixes for each.

For those who prefer a robust solution that integrates functionality with ease of use, dbForge Studio for PostgreSQL stands out. It’s an ideal choice for developers of all skill levels, offering features such as SQL editing, data migration, and database comparison in a single tool.

Explore the full guide about��how to connect to PostgreSQL database and find the right approach for your workflow.

If your organization relies on multiple DBMS solutions, try a DB tool named dbForge Edge that supports Microsoft SQL Server, MySQL, MariaDB, Oracle, PostgreSQL, and a wide range of cloud services.

Or choose an effective solution from Devart's database management tools that fits you best.

[arashtadstudio]

Master the PostgreSQL psql tool with these essential commands and productivity tips for database administration. Mastering psql: The PostgreSQL Command Line Tool Mastering psql: The Essential PostgreSQL Command Line Tool Introduction to psql psql is PostgreSQL's powerful interactive terminal that provides unmatched access to your databases. This guide covers everything from basic navigation to advanced features that will make you more productive. Basic psql Operations 1. Connecting to Databases psql -U pgadmin -d school_db -h 127.0.0.1 This connects using our standard admin credentials to the school database we created earlier. 2. Essential Meta-Commands CommandDescription \\lList all databases \\c dbnameConnect to different database \\dtList tables in current database \\d table_nameDescribe table structure \\xToggle expanded display Query Execution Features 1. Running SQL Commands SELECT * FROM teachers LIMIT 5; End statements with semicolon. Results display in clean tabular format. 2. Query Timing \\timing Toggles execution time measurement for all subsequent queries. 3. Query History \\s Shows command history. Use \\s filename to save to file. Advanced psql Features 1. Auto-Completion Press Tab to complete: Table names Column names SQL keywords Works even mid-query. 2. Output Formatting \\pset format html \\o report.html SELECT * FROM teachers; Exports query results as HTML table. 3. Variables \\set myvar 5 SELECT * FROM students LIMIT :myvar; Use variables in queries for dynamic execution. Scripting with psql 1. Executing Files \\i setup.sql Runs SQL commands from external file. 2. Output Redirection \\o results.txt SELECT * FROM students; \\o Sends query results to file. 3. Conditional Execution \\if :DBNAME == 'school_db' \\echo 'Working on school database' \\endif Customizing Your psql Environment 1. Configuration File ~/.psqlrc Add common settings like: \\set PROMPT1 '%n@%/%R%# ' \\timing on \\x auto 2. Useful Aliases \\set active_students 'SELECT * FROM students WHERE active = true' Create shortcuts for frequent queries. Conclusion You now have a professional's toolkit for psql. Remember these power tips: Use \\? anytime to see available commands Combine meta-commands with SQL for powerful workflows Customize your .psqlrc for your common tasks Leverage output formatting for reports Subscribe to Our YouTube for More Download as PDF

[piembsystech]

Connecting to Amazon Redshift Using Different Clients

Connecting to Amazon Redshift Using Different Clients: Best Practices and Features Hello, fellow Redshift enthusiasts! In this blog post, Connecting to Amazon Redshift Using Different Clients we’ll explore the fascinating world of connecting to Amazon Redshift using different clients — a crucial aspect of efficient database management. From psql for command-line efficiency to SQL Workbench/J for…

[lencykorien]

A Step into the World of Data Mastery: Optimizing Redshift for Seamless Migration

When it comes to handling massive datasets, choosing the right approach can make or break your system’s performance. In this blog, I’ll take you through the first half of my Proof of Concept (PoC) journey—preparing data in Amazon Redshift for migration to Google BigQuery. From setting up Redshift to crafting an efficient data ingestion pipeline, this was a hands-on experience that taught me a lot about Redshift’s power (and quirks). Let’s dive into the details, and I promise it won’t be boring!

Step 1: Connecting to the Mighty Redshift

Imagine having a tool capable of handling terabytes of data with lightning speed—welcome to Amazon Redshift! My first task was to set up a Redshift serverless instance and connect to it via psql.

Here’s how I cracked open the doors to Redshift:psql -h <your-redshift-hostname> -p <port-number> -U <your-username> -d <your-database-name>

Once I was in, I felt like stepping into a data powerhouse. The next step? Building a table that’s smart, efficient, and ready for action.

Step 2: Crafting a Supercharged Table

Tables are the backbone of any database, and in Redshift, designing them smartly can save you a world of pain. I created a users table with a structure optimized for Redshift’s columnar architecture and distribution capabilities:CREATE TABLE users ( user_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100), created_at TIMESTAMP, last_login TIMESTAMP ) DISTSTYLE KEY DISTKEY(user_id) SORTKEY(user_id) ENCODE BYTEDICT;

Here’s what makes this setup special: 

DISTSTYLE KEY & DISTKEY (user_id): The DISTSTYLE KEY setting, combined with DISTKEY(user_id), is a clever way to ensure that the data is distributed evenly across all compute nodes. When you use DISTSTYLE KEY, Redshift distributes the data based on the specified column—in this case, user_id. This minimizes the data shuffling that can happen during query execution, which is especially important for large datasets. By choosing user_id as the distribution key, Redshift can better handle queries that filter or join on this column without needing to move data across nodes. 

SORTKEY (user_id): Sorting the data by user_id helps Redshift quickly locate the records it needs when executing queries that filter by user_id. Think of it like arranging a set of books by author’s last name—if you know the author, you can jump straight to the right section. With the SORTKEY on user_id, Redshift doesn’t have to scan the entire table. It can skip irrelevant data, making retrieval faster. 

Column Compression (ENCODE BYTEDICT): Redshift’s columnar storage and compression capabilities are key to making large datasets manageable. I used the BYTEDICT encoding to compress columns, which stores repeating values as a dictionary. This technique reduces storage space and speeds up query performance because fewer bytes need to be read from disk. It’s a space-saver that also enhances I/O efficiency. 

[ Good Read: Why do I need a sitemap? ]

Step 3: Inserting Test Data—The Fun Begins! 

Why test with boring, clean data when you can simulate the real-world mess? I created 200 rows of diverse data with: 

Skewed User IDs (150–160): Because real-world data isn’t always evenly distributed. This tested how well Redshift handles imbalances. 

NULL Values: Every 10th row had NULL values for created_at and last_login—because life is messy, and so is data. 

Randomized Timestamps: For added realism, I introduced randomized timestamps. Data should feel alive, right? 

Here’s the Python script I wrote to bring this to life: 

 The result? A realistic dataset that was ready to be challenged! 

Step 4: Loading Data Like a Pro with the COPY Command 

When it comes to moving large datasets, Redshift’s COPY command is a game-changer. It’s fast, efficient, and designed for scale. For this PoC, I loaded a compressed CSV file stored in Amazon S3. 

But here’s the twist: 10 records in the file conflicted with existing user_id values in the users table. If I tried loading directly, it would fail. So, I got creative. 

You can check more info about: Redshift for Seamless Migration.

Cloud and DevSecOps Solutions.

Infrastructure & Software Delivery Automation.

DevOps Services.

Iaas/Paas Migration.

Data Warehousing Solutions.

[tutorialwithexample]

Unlocking PostgreSQL Power: Tips, Tricks, and Hands-On Tutorial

Welcome to our PostgreSQL Tutorial! PostgreSQL, often called "Postgres," is a powerful, open-source database system used by developers and businesses worldwide. Whether you’re building a small application or managing large datasets, PostgreSQL is versatile and reliable.

What is PostgreSQL?

PostgreSQL is a relational database management system (RDBMS). This means it organizes data into tables that can be linked by relationships. It supports SQL (Structured Query Language), the standard language for interacting with databases.

Why Choose PostgreSQL?

Open Source: PostgreSQL is free to use and has a strong community support.

Extensibility: It allows you to create custom functions and data types.

Performance: PostgreSQL handles large volumes of data efficiently.

ACID Compliance: Ensures data integrity with reliable transactions.

Getting Started

Installation: Download and install PostgreSQL from the official website. It supports Windows, macOS, and Linux.

Connecting to PostgreSQL: Use the psql command-line tool or GUI tools like pgAdmin to connect and manage your database.

Creating a Database: Use the command CREATE DATABASE followed by your desired database name.

Basic Operations

Inserting Data: Add records using the INSERT INTO command.

Querying Data: Retrieve data with the SELECT statement.

Updating Data: Modify existing records using UPDATE.

Deleting Data: Remove unwanted records with DELETE.

Explore More

To dive deeper into PostgreSQL and explore advanced features, check out this PostgreSQL Tutorial. Happy learning!

By following this PostgreSQL Tutorial, you'll quickly become comfortable managing and querying your databases with ease.

[sandeep2363]

Title: 21 Days of PostgreSQL Learning: A Comprehensive Guide

Title: 21 Days of PostgreSQL Learning: A Comprehensive Guide Day 1: Introduction to PostgreSQL What is PostgreSQL? History and development of PostgreSQL Key features and advantages Installing PostgreSQL on different platforms Day 2: Getting Started with psql Introduction to psql command-line tool Connecting to a PostgreSQL database Basic psql commands for database interaction Exploring…

[kido-ih]

postgres cheatsheet

# start interactive postgres cli psql -U postgres

# connect to database foo \c foo;

# list all databases \l

# list all users \du

# list all tables \dt

# create role 'foo' CREATE ROLE foo;

# create database 'foo' with owner bar CREATE DATABASE foo WITH OWNER bar;

# select id,username,password fields from users table SELECT id,username,password from users

# update field password of id 123 in users table UPDATE users SET password = 'somevalue' WHERE id = '123'; # remove all tables in current database DO $$ DECLARE   r RECORD; BEGIN   FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP     EXECUTE 'DROP TABLE ' || quote_ident(r.tablename) || ' CASCADE';   END LOOP; END $$;

[offcampusjobs4u]

Mastering SQL using Postgresql

Mastering SQL using Postgresql Learn key SQL concepts such as how to create database objects, write effective queries and many more. What you’ll learn Setup Postgres Database using Docker Connect to Postgres using different interfaces such as psql, SQL Workbench, Jupyter with SQL magic etc. Understand utilities to load the data Performing CRUD or DML Operations Writing basic SQL Queries…

[dbajamey]

How to list all available tables in PostgreSQL?

Checking the list of database tables is a common task that might be performed multiple times daily, and major database management systems provide built-in methods for this purpose.

PostgreSQL offers several methods that will illustrate using the SQL Shell (psql) command-line tool and dbForge Studio for PostgreSQL, an advanced PostgreSQL IDE that covers all aspects of database management.

Different ways to list tables in PostgreSQL

Continue reading to learn about:

How to connect to a PostgreSQL database using psql

How to list all tables using psql

How to obtain detailed information about tables

How to list tables using the pg_catalog schema

How to view and manage tables in PostgreSQL GUI tools

[arashtadstudio]

Master the PostgreSQL psql tool with these essential commands and productivity tips for database administration. Mastering psql: The PostgreSQL Command Line Tool Mastering psql: The Essential PostgreSQL Command Line Tool Introduction to psql psql is PostgreSQL's powerful interactive terminal that provides unmatched access to your databases. This guide covers everything from basic navigation to advanced features that will make you more productive. Basic psql Operations 1. Connecting to Databases psql -U pgadmin -d school_db -h 127.0.0.1 This connects using our standard admin credentials to the school database we created earlier. 2. Essential Meta-Commands CommandDescription \\lList all databases \\c dbnameConnect to different database \\dtList tables in current database \\d table_nameDescribe table structure \\xToggle expanded display Query Execution Features 1. Running SQL Commands SELECT * FROM teachers LIMIT 5; End statements with semicolon. Results display in clean tabular format. 2. Query Timing \\timing Toggles execution time measurement for all subsequent queries. 3. Query History \\s Shows command history. Use \\s filename to save to file. Advanced psql Features 1. Auto-Completion Press Tab to complete: Table names Column names SQL keywords Works even mid-query. 2. Output Formatting \\pset format html \\o report.html SELECT * FROM teachers; Exports query results as HTML table. 3. Variables \\set myvar 5 SELECT * FROM students LIMIT :myvar; Use variables in queries for dynamic execution. Scripting with psql 1. Executing Files \\i setup.sql Runs SQL commands from external file. 2. Output Redirection \\o results.txt SELECT * FROM students; \\o Sends query results to file. 3. Conditional Execution \\if :DBNAME == 'school_db' \\echo 'Working on school database' \\endif Customizing Your psql Environment 1. Configuration File ~/.psqlrc Add common settings like: \\set PROMPT1 '%n@%/%R%# ' \\timing on \\x auto 2. Useful Aliases \\set active_students 'SELECT * FROM students WHERE active = true' Create shortcuts for frequent queries. Conclusion You now have a professional's toolkit for psql. Remember these power tips: Use \\? anytime to see available commands Combine meta-commands with SQL for powerful workflows Customize your .psqlrc for your common tasks Leverage output formatting for reports Subscribe to Our YouTube for More Download as PDF

[toyoutonki]

Installing postgres app

#Installing postgres app how to#

#Installing postgres app install#

#Installing postgres app download#

#Installing postgres app mac#

#Installing postgres app how to#

See Removing Existing PostgreSQL Installations on the Postgres.app website for instructions on how to do this. It’s recommended that you remove any pre-existing PostgreSQL installations before installing Postgres.app. If you do, you’ll run into problems, if both versions are trying to use the same port (5432 is the default port). The above instructions assume you don’t already have PostgreSQL running on your Mac. sudo mkdir -p /etc/paths.d &Įcho /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp Remove Existing Installations You can also configure your $PATH to use the included command line tools. This will connect to your default database.Īnother alternative is to use a GUI application, such as pgAdmin, DBeaver, Postico, Azure Data Studio, etc. This will connect to that database using the psql command line interface.Īlternatively, you can launch psql in a separate terminal window and type psql. To connect to a database, double click one of the database icons shown in the above screen. You can start and stop PostgreSQL using the relevant buttons on this panel.

#Installing postgres app install#

When you install Postgres.app, several databases are created by default, including one using your system username. Once you’ve done that, you should see a screen similar to the following:

Double-click the Postgres.app icon (in your Applications folder).

Drag the Postgres.app icon to your Applications folder.ĭone.

#Installing postgres app download#

Download the latest version of Postgres.app from the Postgres.app website.

Here are step-by-step instructions for installing PostgreSQL.app on your Mac. You simply download it and drag it to your Applications folder, just like with any other application.

#Installing postgres app mac#

Postgres.app is a full-featured PostgreSQL installation packaged as a standard Mac application. While you can configure PATH for this to work, there is easier solution.The easiest way to install PostgreSQL on a Mac is by installing Postgres.app. Because this package for the setup expects to find standard Postgres installation on your machine. However this won't work on your local machine. If you already deployed your app on the server with Postgres, you probably installed the psycopg2 package to talk to the database. Click the "Start" button and your server is ready to go. Now all that is left to do is to run the app. Download it, open and move the Postgres.app to Applications folder on your Mac. The most up to date version is "Postgres.app with PostgreSQL 13" as of writing this post in December 2020. You can get the dmg file from the official site. This also assumes you don't have other versions of Postgres installed. There are more than one ways how to install this database on your machine, however downloading Postgres.app is the easiest. We will start with the Postgres part that is not Django specific. So developing this feature without Postgres database would be kind of crazy.Īnyway, let's see how to setup Postgres locally. Add -set flags to the command to connect the installation to the PVC you created and enable volume permissions: helm install release-name repo-name -set persistence.existingClaim pvc-name -set volumePermissions.enabledtrue. Postgres offers powerful full-text search you can use from Django. Install the helm chart with the helm install command. In my case, what kind of forced me to have local PostgreSQL for one of my projects, was search. For example SQLite does not care about length of the text in columns. You can get yourself into a situation where your app works locally but does not start on the server, because there is a small difference in how these two databases work. While this setup is pretty easy (you get configuration for SQLite out of the box) it has some issues. With Django I would say it is pretty common to have SQLite as a developer database and then on the server have Postgres as "the real" production database.

[trustrebel]

Dbeaver redshift

DBEAVER REDSHIFT HOW TO

DBEAVER REDSHIFT PDF

DBEAVER REDSHIFT DRIVERS

So if you need all your data into Amazon Redshift and then a tool for your visualizations then Blendo + Chartio are one of your great choices. They provide a comfortable, light-weight tool for data pulls, so either you use SQL or their drag-and-drop Interactive Mode.Ĭhartio partners with Blendo. Great BI tool out there and Blendo partner. There’s built-in support for Amazon Redshift, RDS, Amazon Aurora, EMR, Kinesis, PostgreSQL, and more. QuickSight can access data from many different sources, both on-premises and in the cloud. Amazon QuickSightĪn Amazon product, fast and can connect to all of Amazon’s products as data sources like Redshift. The list gets updated with new tools, but for our Amazon Redshift guide, we stay to those that work with specifically with Amazon Redshift.įor this reason, we took as a benchmark the Business Intelligence Partners list in Amazon’s Partner Network. Some time ago we wrote a post with the ultimate list of custom dashboards and BI tools. Then connecting a BI tool in an Amazon Redshift cluster is usually, straightforward. It is of most importance to get all your data in Redshift first.

DBEAVER REDSHIFT PDF

It is Open Source universal database tool.Ĭlick here to get our FREE 90+ page PDF Amazon Redshift Guide! Which BI tools can I use to connect a BI tool to Amazon Redshift?

DBEAVER REDSHIFT DRIVERS

RazorSQL ships with the JDBC drivers necessary to connect to Amazon Redshift. JackDB is an SQL IDE that works with PostgreSQL, Amazon Redshift and more. You can download from it’s official site. It is a Mac native and supports database systems derived from PostgreSQL like Amazon Redshift. It has a simple interface that works nicely.

DBEAVER REDSHIFT HOW TO

Read how to connect to Amazon Redshift from here.

Read how to connect to Amazon Redshift here.Ī Windows based, Amazon Redshift specific IDE.

Navicat offers many flavors this is a compact version of Navicat. It is all time classic in the PostgreSQL community and its latest version (as of today) pgAdmin 4, is a complete rewrite of pgAdmin, built using Python and Javascript/jQuery.

Follow these instructions on how to connect to your Amazon Redshift cluster with the psql tool with this guide from Amazon.

You will need your Amazon Redshift cluster endpoint, database, and port to connect. Psql is a terminal-based front end from PostgreSQL, and it is pretty straightforward to use. You may also connect with psql to an Amazon Redshift cluster.

Follow these instructions on how to connect to your Amazon Redshift cluster over a JDBC Connection in SQL Workbench/J from Amazon here.

Read the Installing and starting SQL Workbench/J page.

Download SQL Workbench/J from SQL Workbench.

SQL Workbench/JĪmazon in their guide uses SQL Workbench/J. Though you can connect to your cluster using psql or any SQL IDE that supports PostgreSQL JDBC or ODBC drivers. Which SQL IDE is the best to connect to AWS Redshift?Īmazon does not provide any SQL client tools, but they recommend SQL Workbench/J.

[safarijust]

Kitematic remote host

docker run -rm -link brewformulas_postgres_1:postgres -i postgres is create a new container of the postgres docker image, linking the running postgres empty database (Here it’s important to not have the -t flag in order to avoid the error cannot enable tty mode on non tty input).ssh -C connects to the remote machine and send the data.| ssh -C will push the dumped data through SSH using compression.pg_dump -host=$POSTGRES_PORT_5432_TCP_ADDR -dbname=brewformulas_org_prod -username=postgres will dump the brewformulas_org_prod database content to the stdout.sh -c is used in order to execute the line within the container (otherwise the environment variable POSTGRES_PORT_5432_TCP_ADDR is not yet accessible).docker run -rm -link brewformulasdb:postgres -it postgres will create a new container, linked to the container where the database, to be dumped, is running.(In this case, the first server is an Ubuntu server, so I had to use sudo while on the second server I’m running Debian as root). Sudo docker run -rm -link brewformulasdb:postgres -it postgres sh -c 'pg_dump -host=$POSTGRES_PORT_5432_TCP_ADDR -dbname=brewformulas_org_prod -username=postgres' | ssh -C "docker run -rm -link brewformulas_postgres_1:postgres -i postgres sh -c 'psql -host=172.17.0.31 -dbname=brewformulas_org_prod -username=postgres'"īasically, this command is using the pg_dump command in a container linked to the running database to be dumped, SSH the second server and send the dump data (We’re using the -C flag from SSH for the data compression) and then use the psql command on the remote server in a container in order to populate the database with the dumped data.

[computingpostcom]

For over 30 years of existence and active development, PostgreSQL has earned a strong reputation for robustness, reliability, and performance. PostgreSQL is a free and open-source relational database management system. PostgreSQL 14 is the latest release version of this world’s most advanced open-source database. The latest version, PostgreSQL 14 introduces several new features that help ease developments and administration in implementing data-driven applications. It continues to make improvements in the sector of complex data types, including more accessible JSON access and the support for non-contiguous data ranges. PostgreSQL 14 has played a big role in PostgreSQL’s history of high performance and distributed workloads with improvements in logical replication, query parallelism, high-write workloads, connection concurrency. Features of PostgreSQL 14 PostgreSQL 14 comes with the below features and enhancements: Significant performance increases through parallel queries, heavily concurrent workloads, partitioned databases, logical replication, and vacuuming. OUT parameters can now be used to return data from stored procedures. JSON Conveniences and multiranges- enabling the representation of non-contiguous data ranges. Subscripting operators have been added to the jsonb and hstore types. Updates to B-tree indexes are more efficiently managed, resulting in less index bloat. Supports pipelined queries through libpq, which can significantly increase throughput over high-latency connections. Security enhancements In this guide, we will systematically go through how to install PostgreSQL 14 on Debian 11 | Debian 10. Setup Pre-requisites For this guide, ensure that you have the following: Debian 11|Debian 10 Server. User with sudo access. Ensure that your system is updated. sudo apt update && sudo apt upgrade Then proceed and install the required packages. sudo apt -y install gnupg2 wget vim Step 1 – Install PostgreSQL 14 on Debian 11 | Debian 10 PostgreSQL is available in the default Debian repositories but the available versions are not up to date. Check the available versions using the command: sudo apt-cache search postgresql | grep postgresql In this guide, we are interested in PostgreSQL 14 which is not provided in the default repositories. Therefore, we are required to add a repository that provides the package. sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' Import the GPG key for the added repository. wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Next, update your APT package index. sudo apt -y update Now install PostgreSQL 14 on Debian 11/Debian 10 using the command below. sudo apt install postgresql-14 Verify your PostgreSQL 14 installation as below. $ sudo -u postgres psql -c "SELECT version();" version ----------------------------------------------------------------------------------------------------------------------------- PostgreSQL 14.0 (Debian 14.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row) With a complete installation, PostgreSQL 14 will be initialized. Check the status of the service as below. $ systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Mon 2021-10-25 07:26:42 EDT; 1min 10s ago Process: 3811 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 3811 (code=exited, status=0/SUCCESS) CPU: 1ms Oct 25 07:26:42 debian systemd[1]: Starting PostgreSQL RDBMS... Oct 25 07:26:42 debian systemd[1]: Finished PostgreSQL RDBMS. Step 2 – Connect and Secure PostgreSQL database

In PostgreSQL, there is a concept known as roles used for client authentication. PostgreSQL is set to use ident authentication by default. This method associates roles with matching Linux system accounts. If it exists, then you are able to log in. After the installation, a user account with the name postgres is created and associated with the role postgres. With this user, one is able to log in to the PostgreSQL shell. Amon the multiple authentication methods supported by PostgreSQL include: Password – allows a role to connect by providing a password. Ident – works over TCP/IP connections by obtaining the client’s operating system username with an optional username for mapping. Peer– works similar to ident but supports local connections only. Trust – it uses conditions defined on the pg_hba.conf. A user can connect without password as long as conditions in the conf file are met There are two ways of connecting to the PostgreSQL database; Method 1: Switch to the postgres user. sudo -i -u postgres Now access the PostgreSQL shell with the command. psql Sample Output: postgres@debian:~$ psql psql (14.0 (Debian 14.0-1.pgdg110+1)) Type "help" for help. postgres=# Now in the shell, you can now manage your database. Method 2: In this method, you do not need to switch to the postgres user, you can directly access the PostgreSQL shell with the sudo command below. sudo -u postgres psql Sample Output: $ sudo -u postgres psql psql (14.0 (Debian 14.0-1.pgdg110+1)) Type "help" for help. postgres=# Step 3 – Configure PostgreSQL 14 Instance for Remote Access For the PostgreSQL 14 instance to be accessed remotely, we need to modify the file at /etc/postgresql/14/main/pg_hba.conf First, change the peer identification to trust as below. sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/14/main/pg_hba.conf Then, allow password login. sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/14/main/pg_hba.conf Now allow access from everywhere. sudo vim /etc/postgresql/14/main/pg_hba.conf In the file, add the lines below. # Database administrative login by Unix domain socket local all postgres trust # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 host all all 0.0.0.0/24 md5 # IPv6 local connections: host all all ::1/128 scram-sha-256 host all all 0.0.0.0/0 md5 Now allow PostgreSQL to listen on * by editing the below file. sudo vim /etc/postgresql/14/main/postgresql.conf Edit the file as below. # CONNECTIONS AND AUTHENTICATION ........ listen_addresses='*' For the changes made to apply, you need to restart PosgreSQL. sudo systemctl restart postgresql sudo systemctl enable postgresql Step 4 – User Management in PostgreSQL Database Now that we have configured everything, let’s create a superuser for database management. Connect to the PostgreSQL role. sudo -u postgres psql Create a superuser, admin with password as Passw0rd as below. CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd'; Sample Output: psql (14.0 (Debian 14.0-1.pgdg110+1)) Type "help" for help. postgres=# CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd'; CREATE ROLE postgres=# Manage application users Create a database and grant user privileges to the database. In this guide, we will create a database with the name, test_db and a user test_user with password as dbpassword as below. create database test_db; create user test_user with encrypted password 'dbpassword'; grant all privileges on database test_db to test_user; \q

Connect to the instance from remote host. Verify that the service is running: $ ss -tunelp | grep 5432 tcp LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* uid:117 ino:30522 sk:d cgroup:/system.slice/system-postgresql.slice/postgresql@14-main.service tcp LISTEN 0 244 [::]:5432 [::]:* uid:117 ino:30523 sk:e cgroup:/system.slice/system-postgresql.slice/postgresql@14-main.service v6only:1 If you are using ufw, allow port 5432 through the firewall as below. sudo ufw allow 5432/tcp Now we want to connect to the PostgreSQL 14 instance from a remote host. The syntax is as below. psql 'postgres://:@:/?sslmode=disable' For example, on another host, I’ll try and connect to my PostgreSQL14 instance using the superuser account as below. First, install PostgreSQL 14 on the remote host and proceed to access your PostgreSQL14 instance. psql 'postgres://admin:Passw0rd@192.168.205.4:5432/postgres?sslmode=disable' Sample Output: Conclusion We have successfully walked through how to install PostgreSQL 14 on Debian 11 | Debian 10. In addition to that, we have made configurations to PostgreSQL 14 and also accessed it via a remote host. That was enough learning!