Postgres connect to database linux

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.
Читайте также:  Linux чем сжать архив

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.

postgresql pgadminIII

Clicking on pgAdmin III following screen will come:

postgresql pgadminIII part2

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.

postgresql-pgadminIII part3

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

postgresql-pgadminIII Part 4

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.

Читайте также:  Network manager kali linux настройка

connect to a postgresql database

  • 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.

Log in to Postgre with single command.

For the same result on a Red Hatbased 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.

psql-comand-terminal-program-postgres

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.

Command to get information about your postgre connection.

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:

Читайте также:  What is msi linux

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.py

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.

How to access postgre using PGAdmin 3.

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

How to create new server with PgAdmin3.

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.

query tool to enter and execute queries

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.

Источник

Оцените статью
Adblock
detector