How to connect to postgres

Connect to PostgreSQL Database on Linux, Windows

Connect to PostgreSQL from the command line

Running the PostgreSQL interactive terminal program, called psql, which allows you to interactively enter, edit, and execute SQL commands. At the time of installing postgres to your operating system, it creates an «initial DB» and starts the postgres server domain running. Typically initdb creates a table named «postgres» owned by user «current logged in user name»

At the command line in your operating system, type the following command.

Windows :
In windows, current user doesn’t matter

After accessing a PostgreSQL database, you can run SQL queries and more. Here are some common psql commands

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, sent them to PostgreSQL, and see the query results.

Connect to PostgreSQL database using pgAdmin GUI application

You can also connect to PostgreSQL database using pgAdmin GUI application. Connect to the database at localhost:5432 using the user name postgres and the password supplied.

Clicking on pgAdmin III following screen will come:

Now, double click on PostgreSQL 9.4 under the «Servers Groups». pgAdmin will ask you for a password. You have to supply the password for the postgres user for authentication.

Under the Database(s) on this server section, find the desired database and execute SQL queries:

Connecting to PostgreSQL databases

CONTENT

One of the first things you’ll need to think about when working with a PostgreSQL database is how to connect and interact with the database instance. This requires coordination between the database client — the component you use to interact with the database, and the database server — the actual PostgreSQL instance that stores, organizes, and provides access to your data.

Because of this, you need to understand how to connect as a client by providing the required information to authenticate. In this guide, we’ll cover how to connect to a PostgreSQL database using the native psql command line client — one of the most common and useful ways of interacting with a database instance.

In a companion guide, you can find out how to configure PostgreSQL’s authentication to meet your project’s needs. Consider reading both guides for a more complete picture of how authentication works in PostgreSQL.

If your database client or library requests a connection URI, you may want to look at our guide on understanding PostgreSQL connection URIs instead.

Basic information about the psql client

The psql client, the native command line client for PostgreSQL, can connect to database instances to offer an interactive session or to send commands to the server. It is especially useful when implementing your initial settings and getting the basic configuration in place, prior to interacting with the database through application libraries. In addition, psql is great for interactive exploration or ad-hoc queries while developing the access patterns your programs will use.

The way that you connect depends on the configuration of the PostgreSQL server and the options available for you to authenticate to an account. In the following sections, we’ll go over some of the basic connection options. For clarity’s sake, we’ll differentiate between local and remote connections:

Let’s start with connecting to a database from the same computer.

Connecting to a local database with psql

Without any arguments, the psql command attempts to connect to a Unix socket file to access a local database. It uses your operating system username as the PostgreSQL username and database name that you are trying to connect to.

By default, modern versions of PostgreSQL are configured for something called peer authentication. Peer authentication authenticates users automatically if a valid PostgreSQL user exists that matches the user’s operating system username.

So if your current user is a valid PostgreSQL user on your local database, you can connect by typing:

However, it’s unlikely that your normal operating system username already has an associated PostgreSQL username. So usually, you’ll need to log into PostgreSQL using an operating system username that already has an associated PostgreSQL role.

The easiest way to get a shell as the postgres user on most systems is to use the sudo command. To open a shell session for the postgres user and then log into the database, you can type:

If you don’t need to perform any additional shell commands as the postgres user, you can also just run the psql command directly as the postgres user. This will log you in to a PostgreSQL session immediately instead of taking you to a shell first:

Either of these methods should allow you to log into the postgres PostgreSQL user account.

Connecting to a remote database

For security reasons and because of the reliance on a local socket file, peer authentication cannot be used for remote connections. Instead, users will need to log in using another method.

The available authentication methods vary based on the PostgreSQL instance’s configuration. Most commonly, though, you will be able to authenticate by providing the following pieces of information:

Passing connection information to psql with options

So the basic format for connecting to a remote database typically looks something like this:

The remote server will indicate that it requires a password for most accounts, at which point psql will prompt you for the password. If you authenticate successfully, a new interactive PostgreSQL session will be started.

As an example, we can imagine wanting to connect to a database with the following requirements:

Calling psql with the following options would allow you to authenticate:

Passing connection information to psql with a connection string

This same information can also be encoded into a PostgreSQL connection string. A connection string provides the same information in a single URI string that uses certain characters as delimiters between the different fields.

Connection strings have the following general format:

Each of the fields can be omitted if they are unneeded or if the default values are valid.

We can optionally use a connection string to connect with psql instead of the using the options that we used in our previous example:

The psql tool can use either of these formats, so use whichever you prefer. Other tools or libraries that you encounter might nudge you into relying on one more than the other.

Adjusting a PostgreSQL server’s authentication configuration

If you want to modify the rules that dictate how users can authenticate to your PostgreSQL instances, you can do so by modifying your server’s configuration. You can find out how to modify PostgreSQL’s authentication configuration in this article.

In this guide, we covered PostgreSQL authentication from the client side. We demonstrated how to use the psql command line client to connect to both local and remote database instances using a variety of methods.

Knowing how to connect to various PostgreSQL instances is vital as you start to work the database system. You may run a local PostgreSQL instance for development that doesn’t need any special authentication, but your databases in staging and production will almost certainly require authentication. Being able to authenticate in either case will allow you to work well in different environments.

The PostgreSQL database connector can help you manage PostgreSQL databases from JavaScript and TypeScript applications. Learn how to add Prisma to an existing project or how to start with Prisma from scratch.

Connect To a PostgreSQL Database Server

Summary: in this tutorial, you will learn how to connect to the PostgreSQL Database Server via an interactive terminal program called psql and via the pgAdmin application.

When you installed the PostgreSQL database server, the PostgreSQL installer also installed some useful tools for working with the PostgreSQL database server. In this tutorial, you will learn how to connect to the PostgreSQL database server via the following tools:

1) Connect to PostgreSQL database server using psql

psql is an interactive terminal program provided by PostgreSQL. It allows you to interact with the PostgreSQL database server such as executing SQL statements and managing database objects.

The following steps show you how to connect to the PostgreSQL database server via the psql program:

First, launch the psql program and connect to the PostgreSQL Database Server using the postgres user:

Second, enter all the information such as Server, Database, Port, Username, and Password. If you press Enter, the program will use the default value specified in the square bracket [] and move the cursor to the new line. For example, localhost is the default database server. In the step for entering the password for user postgres, you need to enter the password the user postgres that you chose during the PostgreSQL installation.

Third, interact with the PostgreSQL Database Server by issuing an SQL statement. The following statement returns the current version of PostgreSQL:

Please do not forget to end the statement with a semicolon (;). After pressing Enter, psql will return the current PostgreSQL version on your system.

2) Connect to PostgreSQL database server using pgAdmin

The second way to connect to a database is by using a pgAdmin application. The pgAdmin application allows you to interact with the PostgreSQL database server via an intuitive user interface.

The following illustrates how to connect to a database using pgAdmin GUI application:

First, launch the pgAdmin application.

The pgAdmin application will launch on the web browser as shown in the following picture:

Second, right-click the Servers node and select Create > Server… menu to create a server

Third, enter the server name e.g., PostgreSQL and click the Connection tab:

Fourth, enter the host and password for the postgres user and click the Save button:

Fifth, click on the Servers node to expand the server. By default, PostgreSQL has a database named postgres as shown below:

Sixth, open the query tool by choosing the menu item Tool > Query Tool or click the lightning icon.

Seventh, enter the query in the Query Editor, click the Execute button, you will see the result of the query displaying in the Data Output tab:

Connect to PostgreSQL database from other applications

Any application that supports ODBC or JDBC can connect to the PostgreSQL database server. In addition, if you develop an application that uses a specific driver, the application can connect to the PostgreSQL database server as well:

In this tutorial, you’ve learned how to connect to the PostgreSQL database server by using different client tools including psql and pgAdmin GUI application.

How to connect to PostgreSQL through CLI?

I am trying to access postgresql through the command line. However, whenever it is time for me to enter my password, I get the following error: Fatal: password authentication failed for user RMehta. I am pretty sure the reason that password authentication fails is that the user for my database is postgres, and not RMehta.

The only solution I found was using runas in the command line, but I couldn’t figure how to get runas to work. Thanks a lot for any advice. I am using windows 7, and postgresql 9.3

3 Answers 3

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

For Unix environnement the command line is

For a Windows environment, you may consider replacing «-» with «/»

-U option able you to choose a user to connect with

-h option able you to connect with the TCPIP protocol, you may consider it useless for Windows

First make sure your user have a sudo access if not you can use the below command to add your user as sudo user :-

The change will take effect the next time the user logs in.

Now try running this command :-

if that gives you error follow the below steps it should work.

i) Now go to sudo vim /etc/postgresql/ /main/pg_hba.conf file and look for line that says :

and comment that. Just below that line there must be a commented line that says:

or for older versions it’ll be :-

Uncomment that line.

ii) Now restart the postgres by using any of these commands :-

iii) Now you can simply log into postgres using the following command :

iv) once you’re in you can create any operation you want to in my case i wanted to create a new database you can do the same using below command :

How to Connect to a PostgreSQL Database From Command Line in Linux

Home » Web Servers » How to Connect to a PostgreSQL Database From Command Line in Linux

PostgreSQL is an open source relational database management system.

Psql is an interactive terminal program for working with PostgreSQL. Use psql to edit, automate, and execute queries in PostgreSQL.

pgAdmin is a web interface for managing PostgreSQL databases. It provides a visual, user-friendly environment with a host of practical solutions that make managing databases easy.

In this tutorial, you will learn how to connect to PostgreSQL from the command line using psql or pgAdmin.

How to Connect to PostgreSQL Using psql

Installing PostgreSQL creates a default database and user account, both called ‘postgres.’

To log into the ‘postgres’ user account type the following command in the terminal:

This example shows the command in a Debian-based distribution, Ubuntu.

For the same result on a Red Hat–based system, (e.g., Centos and Fedora) use any of the following commands:

These commands open a bash shell and give the user ‘postgres’ root privileges within that shell.

The same command applies if you have already created a different user and a corresponding database with the same name. If a user called ‘test1’, that role will attempt to connect to a database called ‘test1’ by default.

Note: Check out our in-depth article on different ways to create a Postgres user.

To begin using psql, enter the command:

The following screen confirms you are now able to edit and execute queries in PostgreSQL.

PostgreSQL can support and maintain a large number of databases and users simultaneously. Once you log in, it is easy to confirm the current connection and user information.

Simply enter the command:

The output helps to determine which user and database you are currently interacting with.

How to Access psql Directly Using sudo

It is possible to connect with PostgreSQL directly and bypass the intermediary bash shell.

If you are sure that all the components of your databases and users are correctly configured, you can log into psql directly:

The -u (user) option causes sudo to run the specified command as a user other than root. As with the previous method, you can now work with databases by executing queries.

How to Access PostgreSQL With pgAdmin

The pgAdmin is a graphical tool for managing PostgreSQL databases. After installing and configuring the latest browser version of pgAdmin 4, you need to create an initial pgAdmin user account.

The basic setup requires an email and a password to authenticate access to the web interface.

Once the email and password are defined, access the pgAdmin4 interface by using:

To authenticate, use the email address and password created previously. Once the user interface loads, add a PostgreSQL server by navigating to Servers > Create > Server.

The General and Connection tabs allow you to enter values for your server name and database user credentials.

The Hostname/address is the location of the machine where the PostgreSQL server is running. A connection with your user account is established once you save the information entered. The interface presents an overview of the databases that your user account has access to.

To enter and execute queries, click Tools > Query Tool or press ALT+Shift+Q within the current database.

This article provided two (2) simple solutions on how to connect to a PostgreSQL database.

If you are looking for a terminal-based solution, psql is an excellent choice for speed and effectiveness.

The GUI based pgAdmin provides a secure, user-friendly way to log in, administer, and shape databases to fit your requirements. A graphical interface can be indispensable when working on a host of databases simultaneously.