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.

What Is StrongDM, and How dbForge Studio for PostgreSQL Supports It

Privileged Access Management (PAM) solutions have become increasingly popular due to their ability to address data security concerns with flexible access control. One essential area for employing PAM solutions is using them alongside database clients (from the simplest ones to full-fledged IDEs) to ensure safe access to databases.

StrongDM is a leading PAM solution that supports modern software development infrastructures both on-premises and in the cloud. This article explores the workflow and benefits of using StrongDM together with user-friendly PostgreSQL GUI - dbForge Studio.

Read in full to discover what is StrongDM, and how dbForge Studio for PostgreSQL supports it.

It was fantastic day with #oracle12c & #pgsql (at Subidhanagar)

This PGSQL function loops through the bytes of a text data type and drops any bytes that don’t conform to UTF8 standard:

CREATE OR REPLACE FUNCTION utf8clean(text) RETURNS TEXT AS $$
DECLARE
string ALIAS FOR $1;
i INT := 0;
bytecount INT;
bytes INT[];
byte INT;
tmp BYTEA := decode(‘5c’, 'hex’);  – We just need some valid byte to use for set_byte src
passed BYTEA;
 out TEXT;
BEGIN
IF $1 is NULL OR octet_length($1) = 0
THEN return $1;
END IF;

bytes := ARRAY(
SELECT get_byte(convert_to(string, 'UTF8’), x) FROM generate_series(0, octet_length(string) - 1, 1) x
);

IF array_length(bytes, 1) IS NULL
THEN RETURN $1;
END IF;

bytecount := array_length(bytes, 1);

– Look forward implementation to avoid having to store seen values
FOR i IN 0..bytecount LOOP
byte := bytes[i];

IF byte = 10 OR byte = 13 OR byte BETWEEN 32 AND 127 THEN – 1-byte UTF8
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;
END IF;

IF byte BETWEEN 194 AND 223 THEN – 2-byte UTF8
IF bytes[i+1] BETWEEN 128 AND 191 THEN
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;

passed := passed || set_byte(tmp, 0, bytes[i+1]);
END IF;
i := i + 1;
END IF;

IF byte = 224 THEN – 3-byte UTF8
IF bytes[i+1] BETWEEN 160 AND 191 AND bytes[i+2] BETWEEN 128 AND 191 THEN
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;

passed := passed || set_byte(tmp, 0, bytes[i+1]);
passed := passed || set_byte(tmp, 0, bytes[i+2]);
END IF;
i := i + 2;
END IF;

IF byte BETWEEN 225 AND 236 THEN – 3-byte UTF8
IF bytes[i+1] BETWEEN 128 AND 191 AND bytes[i+2] BETWEEN 128 AND 191 THEN
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;

passed := passed || set_byte(tmp, 0, bytes[i+1]);
passed := passed || set_byte(tmp, 0, bytes[i+2]);
END IF;
i := i + 2;
END IF;

IF byte = 237 THEN – 3-byte UTF8
IF bytes[i+1] BETWEEN 128 AND 159 AND bytes[i+2] BETWEEN 128 AND 191 THEN
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;

passed := passed || set_byte(tmp, 0, bytes[i+1]);
passed := passed || set_byte(tmp, 0, bytes[i+2]);
END IF;
i := i + 2;
END IF;

IF byte BETWEEN 238 AND 239 THEN – 3-byte UTF8
IF bytes[i+1] BETWEEN 128 AND 191 AND bytes[i+2] BETWEEN 128 AND 191 THEN
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;

passed := passed || set_byte(tmp, 0, bytes[i+1]);
passed := passed || set_byte(tmp, 0, bytes[i+2]);
END IF;
i := i + 2;
END IF;

IF byte = 240 THEN – 4-byte UTF8
IF bytes[i+1] BETWEEN 144 AND 191 AND bytes[i+2] BETWEEN 128 AND 191 AND bytes[i+3] BETWEEN 129 AND 191 THEN
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;

passed := passed || set_byte(tmp, 0, bytes[i+1]);
passed := passed || set_byte(tmp, 0, bytes[i+2]);
passed := passed || set_byte(tmp, 0, bytes[i+3]);
END IF;
i := i + 3;
END IF;

IF byte BETWEEN 241 AND 243 THEN – 4-byte UTF8
IF bytes[i+1] BETWEEN 128 AND 191 AND bytes[i+2] BETWEEN 128 AND 191 AND bytes[i+3] BETWEEN 129 AND 191 THEN
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;

passed := passed || set_byte(tmp, 0, bytes[i+1]);
passed := passed || set_byte(tmp, 0, bytes[i+2]);
passed := passed || set_byte(tmp, 0, bytes[i+3]);
END IF;
i := i + 3;
END IF;

IF byte = 244 THEN – 4-byte UTF8
IF bytes[i+1] BETWEEN 128 AND 143 AND bytes[i+2] BETWEEN 128 AND 191 AND bytes[i+3] BETWEEN 129 AND 191 THEN
IF passed is NULL
THEN passed := set_byte(tmp, 0, byte);
ELSE passed := passed || set_byte(tmp, 0, byte);
END IF;

passed := passed || set_byte(tmp, 0, bytes[i+1]);
passed := passed || set_byte(tmp, 0, bytes[i+2]);
passed := passed || set_byte(tmp, 0, bytes[i+3]);
END IF;
i := i + 3;
END IF;
END LOOP;

out := convert_from(passed, 'UTF8’);

RETURN out;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

J'ai lu Unleash the Power of Storing JSON in Postgres via Pocket: An article by Sarah Mei titled “Why you should never use MongoDB” discusses the issues you’ll run into if you try to use a NoSQL database when a relational database would be far superior.

PostgreSQL 9.5 的 UPSERT

在「Upsert Lands in PostgreSQL 9.5 – a First Look」這邊提到了 PostgreSQL 9.5 支援的 UPSERT 操作。

UPSERT 的定義是：

(computing, database) An operation that inserts rows into a database table if they do not already exist, or updates them if they do.

如果不存在就 INSERT，如果存在就 UPDATE，然後取一部分的字變成 UPSERT。由於要偵測「存在」，只能用在有 primary key 或是有 unique 條件時的表格上。

作者給的範例講解了 PostgreSQL 9.5 上的語法：

View On WordPress

才接触这玩意，不太熟悉

psql -U 用户名 db名

\d        —> 显示所有表

\d 表名  —> 显示这个表结构

\q    —> 退出

除法：

y/x  在PG里如果想做除法并想保留小数，但"/“ 运算结果为取整，并且会截掉小数部分

可以像这样：

select round(y::numeric/x::numeric,2);

it’s been a while since the last time i played with Postgresql, i’ve recently installed Postgresql on my vmware ubuntu instance.
if you want to connect to your postgresql server via Host, you have to change network configuration from NAT to Bridged, that way your instance could handle connection as its were a standalone machine on the local network.
after that, you have to set postgresql.conf, change “listen_addresses” option (mine was ’*’).

$ nmap 192.168.1.79
Nmap scan report for 192.168.1.79
Host is up (0.0012s latency).
Not shown: 997 closed ports
PORT STATE SERVICE
21/tcp open ftp
22/tcp open ssh
5432/tcp open postgresql

and then you have to change pg_hba.conf, add the IP address that you want add as a ‘client’ that will connect to this server, for example:

host all all 192.168.1.67/24 trust

and you’re done. now you can connect using whatever available pg client.

$ psql -U postgres -h 192.168.1.79 -d postgres
psql (9.0.5, server 9.1.3)
WARNING: psql version 9.0, server version 9.1.
Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

PS:
if got this error
psql: FATAL: Peer authentication failed for user "postgres"
edit pg_hba.conf change this line :
local all all Peer
to
local all all trust

Introduction

PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license. PostgreSQL contains many advanced features, is very fast and standards compliant.

PostgreSQL has bindings for many programming languages such as C, C++, Python, Java, PHP, Ruby… It can be used to power anything from simple web applications to massive databases with millions of records.

Installation Process

To install PostgreSQL you can Ubuntu Software Center or command. Here I’m going through command line

[[MORE]]

First install PostgreSQL server application

Server application is core application of any database applications.

$ sudo apt-get install postgresql

Client Installation

Install the PostgreSQL client to connect remotely with PostgreSQL server that may be on same localhost or yourdomain.com or on some IP address only.

$ sudo apt-get install postgresql-client

That’s all, PostgreSQL has been installed and ready to use.

But you may install more available tools for PgSQL (PostgreSQL) for easy and fast work like pgAdmin III or phpPgAdmin

Now install some Administration tools like pgAdmin or phpPgAdmin. pgAdmin is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

Install pgAdmin III

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL.

$ sudo apt-get install pgadmin3

Note: pgAdmin is desktop GUI application.

or Install phpPgAdmin

phpPgAdmin is a web-based administration tool for PostgreSQL. It is perfect for PostgreSQL DBAs, newbies and hosting services.

$ sudo apt-get install phppgadmin

After installation target your browser on: http://127.0.0.1/phppgadmin

Note: phppgadmin is web-app and while you are installing this will also install apache2, php5 and other required applications.

Basic Server Setup

To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

Use terminal for below settings:

First login to postgres as superuser by using sudo or after login to root user.

$ sudo -u postgres psql postgres

And set password for user postgres. Prompt will be as postgres=#

postgres=# \password postgres
Enter new password: 
Enter it again: 

… That’s all …

Now you may proceed with your PostgresSQL, see help for available query and command on PgSQL.

Type “\help” for more information

postgres=# \help
postgres=# \help CREATE DATABASE
postgres=# \help CREATE USER

How to exit to help or other displaying by psql command line interface:

Type q to quit for everything displaying after some query like after select command or displaying help.

To exit from CLI just type \q to quit from psql CLI application.

Ubuntu 11.10부터는 PostgreSQL 8.4 버전이 폐기되고, 최신 버전인 9.1을 지원한다. 업그레이드하기 전에 이미 우분투에서 8.4를 사용 중이었다면, 11.10으로 업그레이드하는 도중에 아마 몇 가지 안내와 함께 PostgreSQL 9.1이 설치되었을 것이다. 이대로 그냥 구 버전을 계속 쓸 수도 있지만, PostgreSQL의 최신 버전을 쓰는 데에는 여러 이점이 있기 때문에 업그레이드를 해 보도록 한다.

우선, 원활한 업그레이드를 위해서 양쪽 버전의 PostgreSQL 클러스터에 모두 접근할 수 있는 계정에서 작업하는 것이 좋다.

$ sudo su - postgres

PostgreSQL 9.1이 설치되면서 기본으로 빈 클러스터 하나가 자동으로 만들어졌을 텐데, 먼저 이 클러스터를 제거한다.

postgres$ pg_dropcluster --stop 9.1 main

아래의 명령을 실행하면 8.4 버전의 클러스터와 같은 내용의 9.1 클러스터가 만들어진다.

postgres$ pg_upgradecluster -v 9.1 8.4 main

이제 필요없는 구 버전의 클러스터는 아래 명령으로 제거할 수 있다.

postgres$ pg_dropcluster --stop 8.4 main

끝으로 postgresql-8.4 패키지도 삭제하고 업그레이드를 마무리하자.

$ sudo apt-get remove postgresql-8.4

Installing PostgreSQL on Mac using MacPorts

Aunque no me gusta mucho Posgtres a veces toca hacer estas operaciones

Para hacer backup de una BD cualquiera

pg_dump nombre_db  -U usuario  > archivo.sql

Para restaurarlo

psql -d nombre_db -U usuario -h localhost < archivo.sql

I have a SHP file of a city-level detail which I would like to edit. In order to do that, I’m juggling between uDig and QGIS to compare usability. I’m focusing on QGIS here, because it seems a lot of folks give it props. Here, from beginning to end, is what I did:

1. I have had no success finding satellite imagery of my city - Vancouver - through searching their list of WMS servers (the task does make me wish I was targeting Germany though).
2. I decided to look for a repository OSM/Mapnik maps… no luck there either.
3. I looked up “OSM + file” and found that there are snapshot dumps of OSM maps for download. Yes! However, the closest thing I find to Vancouver is at country-level. Alright, I’ll bite. It’s a 750meg PBF file, oh my…
4. I looked for a way to import that to QGIS and realized that while it supports OSM maps, it supports OSM maps in XML format. As of this date, OSM has already deprecated XML and switched to PBF. Great.
5. I browsed around looking for “QGIS + PBF” and came across osm2pgsql. This little tool takes in OSM PBF and converts/dumps the data to a PostgreSQL database, which QGIS can import!
6. I’m only versed in MySQL so I had to install PostgreSQL server and PostGIS. I forgot to mention that I’m on OSX so getting the database running took a couple of hours longer than usual.
7. Now finally I could get back to step 4 and did something with the PBF. This was my input:

   osm2pgsql -d canada_pbfimport -U postgres -H localhost canada.osm.pbf

   And the output:

   osm2pgsql SVN version 0.70.5
   
   Using projection SRS 900913 (Spherical Mercator)
   Setting up table: planet_osm_point
   NOTICE:  table "planet_osm_point" does not exist, skipping
   NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
   Setting up table: planet_osm_line
   NOTICE:  table "planet_osm_line" does not exist, skipping
   NOTICE:  table "planet_osm_line_tmp" does not exist, skipping
   Setting up table: planet_osm_polygon
   NOTICE:  table "planet_osm_polygon" does not exist, skipping
   NOTICE:  table "planet_osm_polygon_tmp" does not exist, skipping
   Setting up table: planet_osm_roads
   NOTICE:  table "planet_osm_roads" does not exist, skipping
   NOTICE:  table "planet_osm_roads_tmp" does not exist, skipping
   Mid: Ram, scale=100
   
   Reading in file: canada.osm.pbf
   Processing: Node(71446k) Way(4608k) Relation(52k)  parse time: 1269s
   
   Node stats: total(71446261), max(1254505941)
   Way stats: total(4608725), max(109616802)
   Relation stats: total(52638), max(1553135)
   
   Writing way(4608k)
   
   Writing rel(52k)
   Committing transaction for planet_osm_roads
   Committing transaction for planet_osm_point
   Sorting data and creating indexes for planet_osm_roads
   Sorting data and creating indexes for planet_osm_point
   Committing transaction for planet_osm_line
   Sorting data and creating indexes for planet_osm_line
   Committing transaction for planet_osm_polygon
   Sorting data and creating indexes for planet_osm_polygon
   Completed planet_osm_roads
   Completed planet_osm_point
   Completed planet_osm_line
   Completed planet_osm_polygon
   
   

8. Now finally back in QGIS, upon quesstimating which table I should be importing, I picked one called public.planet_osm_polygon. After thinking a bit, it gave me this error:

   The table has no column suitable for use as a key.
   
   Quantum GIS requires that the table either has a column of type int4 with a unique constraint on it (which includes the primary key), has a PostgreSQL oid column or has a ctid column with a 16bit block number.

9. Argh!!!