Hot answers tagged

46

“Elephant” icon menu When pgAdmin is running, an elephant icon menu appears. Look at the upper right corner of the screen. The elephant refers to Slonik, the Postgres mascot. Choose New pgAdmin Window menu item. FYI, pgAdmin 4 is built as a web app, not a desktop app, even when run locally on your own computer. Note that the internal web server provided ...


29

Configure pgAdmin->Paths->Binary paths and set "PostgreSQL Binary Path" as shown in the screenshot. Depending on your OS and installation details, the binaries may be located elsewhere. Try `which psql` from the command line on linux/Unix systems. A Windows example: PostgreSQL Binary Path: "C:\Program Files\PostgreSQL\9.6\bin"


24

Under Windows, pgAdmin4 stores the server list in the users directory in a SQLLite 3 format. Here: %your-user-home%\AppData\Roaming\pgAdmin\pgadmin4.db


22

Select "Servers" item. Select "Configure PgAdmin" Enter to local path Postgresql "bin" folder. On OSX the path is /Applications/Postgres.app/Contents/Versions/latest/bin On Mac OSX Sierra the path was /Library/PostgreSQL/9.6/bin - I believe this is the default location from the EnterpriseDB installer for 9.6 that automatically installs PgAdmin 4. On Linux ...


21

It is worth mentioning that you are free to define any existing database in the cluster as "Maintenance DB". You are not limited to the options suggested by the interface, which are just typical defaults. This is particularly useful for users with limited permissions or read-only access. Access to a single DB is simpler with the same DB as "Maintenance DB". ...


18

I found nothing in the pgAdmin documentation, but the source code reveals the query behind these entries (added for Postgres 9.2+): It boils down to: SELECT temp_files AS "Temporary files" , temp_bytes AS "Size of temporary files" FROM pg_stat_database db; And the Postgres manual has details for pg_stat_database: tmp_files bigint ...


18

To get all members of all roles: SELECT r.rolname as username,r1.rolname as "role" FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1; Add r1.rolname='council_stuff' to filter on only that one. Be aware that users ...


16

Under Windows this information is stored in the Registry under the key: HKEY_CURRENT_USER\Software\pgAdmin III In Linux systems I would expect some file in the user home directory.


16

If you want to do this in PGAdmin, it is much easier than using the command line. It seems in PostgreSQL, to add a auto increment to a column, we first need to create a auto increment sequence and add it to the required column. I did like this. 1) Firstly you need to make sure there is a primary key for your table. Also keep the data type of the primary ...


14

There are instructions on the pgAdmin site how to install with pip: https://www.pgadmin.org/download/pip4.php But there is no official Debian Package (.deb), yet. A request for packaging is pending: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=834129 For now, consider the Postgres Apt repository: https://wiki.postgresql.org/wiki/Apt/FAQ


13

I'm assuming that you want to create this extension in another server. I'm guessing that the server didn't get the "postgresql contrib" package installed. This package contains the "standard" extensions that are available from the PostgreSQL source code. I think you need to get the system administrator to install the required package for your OS. For ...


13

In Ubuntu 13.10 pgAdmin stores configuration in user's home directory: /home/<<username>>/.pgadmin3 or shorter ~/.pgadmin3 Note that this file is hidden, so you may need to enable some kind of "Show hidden files" option in your file manager.


12

According to: http://www.postgresql.org/message-id/BLU0-SMTP179B92C5102247CD961A4B3CF2A0@phx.gbl The temp counter (files and space used) shows a total of all temp files used since probably cluster creation. It does not reflect the current space used by temp files. My system for example shows almost 700GB of temp files used, but the actual space taken up ...


12

This nomenclature all comes down to knowing about MVCC, or Multi-Version Concurrency Control, which is how PostgreSQL handles transactions and concurrency. When you update or delete a row, doesn't actually remove the row. When you do an UPDATE or DELETE, the row isn't actually physically deleted. For a DELETE, the database simply marks the row as ...


11

From the docs: The maintenance DB field is used to specify the initial database that pgAdmin connects to, and that will be expected to have the pgAgent schema and adminpack objects installed (both optional). On PostgreSQL 8.1 and above, the maintenance DB is normally called ‘postgres’, and on earlier versions ‘template1’ is often used, though it ...


11

pgAdmin (both III and 4) execute both statements. For the first one, it just discards the output, unless there is an error. You can actually chain as many statements as you want: -- We create one table CREATE TABLE digit_names ( digit integer PRIMARY KEY, digit_name text, UNIQUE (digit_name) ) ; -- Fill it with values INSERT INTO ...


11

Go to File-> Reset layout This will merge the outputpane to the bottom of the screen.


10

In pgAdmin… To edit the postgresql.conf file:Choose Tools > Server Configuration > postgresql.conf To edit the pg_hba.conf file:Choose Tools > Server Configuration > pg_hba.conf Avoid the red-herring File menu: File > Open postgresql.conf File > Open pg_hba.conf


10

This is pretty easy in pgAdmin 4. First, add a column to your table, then click the little edit icon: Then go to Constraints and select the Identity type: Save your table and the column will auto-increment.


10

Based on Erwin solution: SELECT conrelid::regclass AS "FK_Table" ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in pg_get_constraintdef(c.oid))-14) END AS "FK_Column" ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), ...


10

Debunking If you are doing all your updates in the same transaction, each of them will have to work an increasingly bigger set of (physical) tuples. See the following example: CREATE TABLE explode (id integer, something text); INSERT INTO explode SELECT i, md5(i::text) FROM generate_series(1, 100000) t(i); \dt+ explode -- done in psql ...


9

No need to parse pg_get_constraintdef(), just use columns of pg_constraint table to obtain other details (the docs). Here constraint_type can be: p - primary key, f - foreign key, u - unique, c - check constraint, x - exclusion, ... Based on Erwin's answer: SELECT c.conname AS constraint_name, c.contype ...


9

There is a setting for that in the options: Max characters per column - useful when dealing with big columns. Obviously your setting is 256 characters. Set it higher or set it to -1 to disable the feature. Consult the fine manual here for more details.


9

A PostgreSQL server connection dropping after 10-15 minutes is almost certainly being caused by a state-tracking firewall (possibly using Network Address Translation (NAT)) between the client and the server. Many such firewalls have default timeouts of 15 minutes (900 seconds). The three server-side parameters, tcp_keepalives_idle, tcp_keepalives_interval, ...


9

Open pgAdminIII, Select your database, Use the Magnifying Glass button, i.e. "Execute arbitrary SQL queries" Instead of running your query using the green triangle "Execute Query", choose the button two to the right of that - i.e. "Execute Query, write result to file". Choose your destination, then you can view arbitrary length text in your chosen file ...


9

sudo apt autoremove pgadmin4 works for ubuntu version 18.04


9

The query is hard-coded into pgAdmin III, and you cannot change it. These columns have been removed in v12 because they are no longer useful. pgAdmin III has been out of support for years. Use pgAdmin 4 or another supported client tool.


8

When a cursor is defined at the SQL level with DECLARE, there is an option WITH HOLD that makes it continue to exist after commiting the current transaction. Quoting the doc: WITH HOLD specifies that the cursor can continue to be used after the transaction that created it successfully commits On the other hand, a refcursor opened by a plpgsql function ...


8

Your client (Windows) is running on a different machine than your server (Ubuntu). You cannot read the remote file system. Inspecting the logs only works if your client is on the same machine (or has access to the remote file system by way of some advanced trickery.) To suppress the error message remove the log file from the display. In the Server Status ...


8

What you had originally was a correct syntax - for tables, not for schemas. As you did not have a table (dubbed 'relation' in the error message), it threw the not-found error. I see you've already noticed this - I believe there is no better way of learning than to fix our own mistakes ;) But there is something more. What you are doing above is too much ...


Only top voted, non community-wiki answers of a minimum length are eligible