Connect to PostgreSQL Database on Linux, Windows
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.
Debian based systems like Ubuntu :
Connect/login as root —
[email protected]:~$ sudo -i -u postgres [email protected]:~$ psql psql (9.3.5, server 9.3.6) Type "help" for help.
Redhat based systems like Centos / Fedora :
Connect/login as root —
[email protected]:~$ su - postgres [email protected]:~$ psql psql (9.3.6) Type "help" for help.
Windows :
In windows, current user doesn’t matter
C:\Program Files\PostgreSQL\9.4\bin>psql -U postgres Password for user postgres: psql (9.4.1) Type "help" for help. postgres=#
After accessing a PostgreSQL database, you can run SQL queries and more. Here are some common psql commands
- To view help for psql commands, type \?.
- To view help for SQL commands, type \h.
- To view information about the current database connection, type \conninfo.
- To list the database’s tables and their respective owners, type \dt.
- To list all of the tables, views, and sequences in the database, type \z.
- To exit the psql program, type \q.
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.
psql [option. ] [dbname [username]]
| Option | Description |
|---|---|
| -a —echo-all | Print all nonempty input lines to standard output as they are read. This is equivalent to setting the variable ECHO to all. |
| -A —no-align | Switches to unaligned output mode. |
| -c command —command=command | Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option. |
| -d dbname —dbname=dbname | Secifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line. |
| -e —echo-queries | Copy all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries. |
| -E —echo-hidden | Use the file filename as the source of commands instead of reading commands interactively. After the file is processed, psql terminates. This is in many ways equivalent to the meta-command \i. |
| -F separator —field-separator=separator | Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f. |
| -h hostname —host=hostname | Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket. |
| -H —html | Turn on HTML tabular output. This is equivalent to \pset format html or the \H command. |
| -l —list | List all available databases, then exit. Other non-connection options are ignored. This is similar to the meta-command \list. |
| -L filename —log-file=filename | Write all query output into file filename, in addition to the normal output destination. |
| -n —no-readline | Do not use Readline for line editing and do not use the command history. This can be useful to turn off tab expansion when cutting and pasting. |
| -o filename —output=filename | Put all query output into file filename. This is equivalent to the command \o. |
| -p port —port=port | Specifies the TCP port or the local Unix-domain socket file extension on which the server is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432. |
| -P assignment —pset=assignment | Specifies printing options, in the style of \pset. Note that here you have to separate name and value with an equal sign instead of space. For example, to set the output format to LaTeX, you could write -P format=latex. |
| -q —quiet | Specifies that psql should do its work quietly. By default, it prints welcome messages and various informational output. If this option is used, none of this happens. This is useful with the -c option. This is equivalent to setting the variable QUIET to on. |
| -R separator —record-separator=separator | Use separator as the record separator for unaligned output. |
| -S —single-line | Runs in single-line mode where a newline terminates an SQL command, as a semicolon does. |
| -t —tuples-only | Turnoff printing of column names and result row count footers, etc. |
| -T table_options —table-attr=table_options | Specifies options to be placed within the HTML table tag. See \pset for details. |
| -U username —username=username | Connect to the database as the user username instead of the default. (You must have permission to do so, of course.) |
| -v assignment —set=assignment —variable=assignment | Perform a variable assignment, like the \set meta-command. Note that you must separate name and value if any, by an equal sign on the command line. |
| -V —version | Print the psql version and exit. |
| -w —no-password | Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password. |
| -W —password | Force psql to prompt for a password before connecting to a database. |
| -x —expanded | Turn on the expanded table formatting mode. |
| -X, —no-psqlrc | Do not read the start-up file. |
| -z —field-separator-zero | Set the field separator for unaligned output to a zero byte. |
| -0 —record-separator-zero | Set the record separator for unaligned output to a zero byte. This is useful for interfacing, for example, with xargs -0. |
| -1 —single-transaction | When psql executes a script with the -f option, adding this option wraps BEGIN/COMMIT around the script to execute it as a single transaction. This ensures that either all the commands complete successfully, or no changes are applied. |
| -? —help | Show help about psql command line arguments and exit. |
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:
Follow us on Facebook and Twitter for latest update.
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook
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.
- This guide assumes that you have already installed PostgreSQL and created a database you want to access.
- Access to a command-line/terminal window
- Sudo or root privileges
- pgAdmin 4 installed
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.
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.
python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.pyOnce 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.