Dbeaver как подключиться к базе postgres

Guide to DBeaver

DBeaver is a database manager. DBeaver lets you add rows in its GUI without writing any SQL. This guide walks you through a number of basic tasks commonly encountered in managing LTER-core-metabase.

Installation

1. Install Postgres for Windows using the EnterpriseDB installer.
2. Install DBeaver.
3. Run DBeaver and connect to your Postgres instance.

Backup a database to SQL file

1. In DBeaver, right-click the database and click Tools > Backup.
2. Select schemas to include and click Next.
3. For Format, choose Plain.

Optional: You might choose to make the backup without ownership or privilege statements. This makes it easier to restore to a different server. However, note that the user restoring the database from SQL backup will become the new owner.

How to Connect PostgreSQL using SQL Developer, Visual Studio and DBeaver

So, you’ve installed your Postgres database, whether Community Edition, EDB Postgres Advanced Server or one of many other cloud, container, or Database as a Service offerings out there. Now what next?

Well, the first thing you are going to do (once you have secured your new database) is access it, and use it to store and distribute your data. Which means you are going to need a design and development environment in which to do so. But where to begin?

There are many ways to access Postgres databases. Many database designers, developers and administrators use IDEs (Integrated Development Environment) as tools to access the internals of their database estate.

The good news is that even if you use an IDE for another database, chances are that you will be able to use it for Postgres as well.

I’m going to demonstrate connectivity to an EDB Postgres Advanced Server from 64-bit Windows 10, but this is just as applicable to Postgres Community Edition and other Postgres-based databases too.

Before I connected to the database, I created an instance on port 5444, adjusted the security settings to allow external connections, and created a pgbench database, a pgbench superuser, and ran the pgbench executable as follows:

This will give me something to connect to, and something to look at, once I have connected the various IDEs.

How to connect to SQL*Developer with PostgreSQL

This is a commonly used tool by Oracle professionals, and it can also be used to maintain a variety of other databases, Postgres included.

SQL*Developer can be downloaded from the Oracle website and is a Java-based tool that will run on pretty much any platform that runs Java, or even OpenJDK.

Download the appropriate zip bundle, unzip somewhere you can execute from, and run the sqldeveloper.exe file.

You should get a screen like this (you may already have SQL*Developer installed, in which case you can skip the download step).

To connect to pretty much any kind of Postgres database, you are going to need a postgresql JDBC driver.
Download the appropriate driver for the version of SQL*Developer you are running (Help -> About -> Java Platform)

I am on Java v1.8, so I downloaded the PostgreSQL JDBC 4.2 Driver, 42.2.12.

Then, put the JDBC .jar in your SQL*Developer Java lib/ext directory. Mine was here:

Now you are ready to connect to your database.

Right-click on “Oracle Connections” in the left tree, and select “New Connection”.

You should get a dialog that looks like this:

Select Database Type -> PostgreSQL from the drop-down list, and enter your connection details, as below:

And your database objects are now shown in the tree on the left.

You can now view table data, metadata, and issue SQL commands as usual.

How to connect to Visual Studio Code with PostgreSQL

Visual Studio Code is a lightweight source code editor which runs on your desktop and is available for Windows, macOS and Linux.

Visual Studio Code has a wide variety of plugins, including many for PostgreSQL.

Below is a screenshot of Visual Studio Code with the Microsoft “PostgreSQL for Visual Studio Code” plugin highlighted and ready to install — just press the green “install” button and.

You should get a download and install completion dialog, as below:

You are now ready to start entering and executing your SQL.

Open a file (File -> New File) and set the file type to SQL (View -> Command Palette -> PostgreSQL: Manage Connection Profiles, and opt to select SQL as your file type, then select SQL from the list). Your file should now have the red barrel SQL icon on the tab.

Now create a Connection Profile. Follow the View -> Command Palette -> PostgreSQL: Manage Connection Profiles wizard — and make sure to include the database to connect to. You are now ready to execute the SQL in your script.

Type out your query (you will get autocomplete options as you type) and right-click and select Execute Query. You will see the results of your query in the right-hand pane.

How to connect to DBeaver with PostgreSQL

DBeaver is an amazing IDE which will allow you to connect to virtually anything with a JDBC connector.

It is available for download at https://dbeaver.io/download/ in both community edition (free) and as a paid subscription version offering yet more connectivity and functionality.

After downloading the installer, start the installation process and follow the instructions. This should all be pretty straightforward.

Once you have completed the installation, start the DBeaver application from your start menu. You should see a loading splash screen and then the main application will open, like so:

To connect to your PostgreSQL database, select Database -> New Database Connection and select PostgreSQL (with the elephant):

If you don’t have the JDBC drivers, you can add them on the next screen:

Enter your server login details, and add the JDBC driver by clicking the “Edit Driver Settings”, if you haven’t already done so:

You can check your connection is set up correctly by clicking on the “Test Connection . ” button at the bottom left of the window.

You can now achieve a variety of tasks, including using the tree on the left to drill down into objects, right click to (for example) query data in tables, edit data on the result grid, display relationship diagrams, and more.

Connect to PostgreSQL Data in DBeaver

Rapidly create and deploy powerful Java applications that integrate with PostgreSQL-compatible database engines.

Manage PostgreSQL data with visual tools in DBeaver like the query browser.

The CData JDBC Driver for PostgreSQL implements JDBC standards that enable third-party tools to interoperate, from wizards in IDEs to business intelligence tools. This article shows how to connect to PostgreSQL data with wizards in DBeaver and browse data in the DBeaver GUI.

Create a JDBC Data Source for PostgreSQL Data

Follow the steps below to load the driver JAR in DBeaver.

1. Open the DBeaver application and, in the Databases menu, select the Driver Manager option. Click New to open the Create New Driver form.
2. In the Driver Name box, enter a user-friendly name for the driver.
3. To add the .jar, click Add File.
4. In the create new driver dialog that appears, select the cdata.jdbc.postgresql.jar file, located in the lib subfolder of the installation directory.
5. Click the Find Class button and select the PostgreSQLDriver class from the results. This will automatically fill the Class Name field at the top of the form. The class name for the driver is cdata.jdbc.postgresql.PostgreSQLDriver.
6. Add jdbc:postgresql: in the URL Template field.

Create a Connection to PostgreSQL Data

Follow the steps below to add credentials and other required connection properties.

1. In the Databases menu, click New Connection.
2. In the Create new connection wizard that results, select the driver.
3. On the next page of the wizard, click the driver properties tab.

Enter values for authentication credentials and other properties required to connect to PostgreSQL.

To connect to PostgreSQL, set the Server, Port (the default port is 5432), and Database connection properties and set the User and Password you wish to use to authenticate to the server. If the Database property is not specified, the data provider connects to the user’s default database.

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the PostgreSQL JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.postgresql.jar

Fill in the connection properties and copy the connection string to the clipboard.

Below is a typical connection string:

Query PostgreSQL Data

You can now query information from the tables exposed by the connection: Right-click a Table and then click Edit Table. The data is available on the Data tab.

CData Software is a leading provider of data access and connectivity solutions. Our standards-based connectors streamline data access and insulate customers from the complexities of integrating with on-premise or cloud databases, SaaS, APIs, NoSQL, and Big Data.

Подключение к PostgreSQL с локального компьютера

У ряда хостинг-провайдеров доступна возможность небезопасного подключения к PostgreSQL с домашнего или рабочего компьютера. На нашем хостинге в целях безопасности удаленное подключение к PostgreSQL запрещено.

Но присутствует возможность использовать SSH-туннель до сервера хостинга. Это позволит обеспечить зашифрованное соединение с базой данных PostgreSQL.

В данном примере для удаленной работы с PostgreSQL разберем подключение с помощью DBeaver.

1. Подготовка к работе

Для настройки подключения нам понадобятся реквизиты подключения по SSH, включающие в себя

• ip-адрес сервера (1)
• логин (2)
• пароль (3)

Данные реквизиты должны были прийти на вашу электронную почту при создании контейнера хостинга. Если не можете найти это письмо, реквизиты можно получить в панели управления хостингом. Пароль можно сбросить и получить новый.

Так же нам понадобится ip-адрес PostgreSQL-сервера, который мы можем определить следующим образом.

В настройках сайта переходим на вкладку PostgreSQL и получаем имя хоста (сервера).

В разделе Файлы запускаем Терминал

В терминале набираем команду ping «имя хоста PostgreSQL» (1), в нашем случае

и смотрим ip-адрес, который отвечает на наши запросы (2).

2. Настройка DBeaver

Последнюю версию DBeaver можно скачать с сайта dbeaver.io .

Запустив DBeaver выбираем в меню «Создать новое подключение». Выбираем PostgreSQL.

Далее настраиваем SSH-туннель:

• Ставим галочку «Использовать тунель SSH»
• Вводим IP-адрес из параметров подключения по SSH
• Вводим имя пользователя (логин)
• Пароль
• Жмем на кнопку «Тест соединения»

При первом подключении появится запрос относительно необходимости принять и сохранить ключ сервера в списке постоянных ключей. Ответьте согласием на этот вопрос.

• При успешном подключении – должно появиться такое окно

Возвращаемся на вкладку «Главная»

• В поле «Хост» пишем ip-адрес сервера, по которому отзывался PostgreSQL в Терминале
• В поле База данных – имя нашей базы данных из настроек PostgreSQL
• Пользователь и пароль из настроек PostgreSQL
• Нажимаем «Тест соединения»
• При успешном подключении – должно появиться такое окно

Теперь возможно работать с базой данных PostgreSQL удаленно со своего компьютера, используя DBeaver.

Инструкция подготовлена пользователем услуги Хостинг.