The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Hot answers tagged

31

I found this answer elsewhere. If your DB is on the local host, try leaving the host field blank in the connection as opposed to using “localhost” or “127.0.0.1″. This tells PgAdmin to connect via the local unix socket instead of TCP.


30

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


28

You can use the function pg_get_constraintdef(constraint_oid) in a query like the following: SELECT conrelid::regclass AS table_from , conname , pg_get_constraintdef(oid) FROM pg_constraint WHERE contype IN ('f', 'p ') AND connamespace = 'public'::regnamespace -- your schema here ORDER BY conrelid::regclass::text, contype DESC; Result: ...


25

The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp connections. Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 3561/postgres So it can only accept local tcp connections regardless of what ...


19

run: psql -U your_user_name your_db_name < your_dump_file


18

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"


15

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.


14

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

The lack of access to temporary tables in other sessions is not a matter of permissions, it's a technical limitation of the design. A PostgreSQL backend can't access temporary tables of another backend because none of the usual housekeeping to allow concurrent access is done for temporary tables. In 9.2 you will want to use an UNLOGGED table instead; this ...


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

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


12

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 Number of ...


11

You write: Each customer can have multiple sites, but only one should be displayed in this list. Yet, your query retrieves all rows. That would be a point to optimize. But you also do not define which site is to be picked. Either way, it does not matter much here. Your EXPLAIN shows only 5026 rows for the site scan (5018 for the customer scan). So ...


11

We had the same problem 2 times on two different computer, in just a few days of interval. PgAdmin started to crash each time we open query builder. Restarting the computer, reinstalling pgAdmin with differents versions... Nothing resolved the problem. But we found the solution to this problem! PgAdmin uses a file where it saves the last queries (kind of ...


11

The short answer is "No". Temporary tables in other sessions are invisible by design. It makes no difference if two sessions have the same user. Even: The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables


11

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


10

I can connect to my postgres instance from pgAdmin III without a password for any user including superusers such as postgres. Because you are connecting ok from another client, there is no reason you should not be able to connect from pgAdmin if they are on the same workstation - unless some firewall rule on the client itself is allowing one program but ...


10

pgScript is a local script extension of pgAdmin, which you most probably do not want here. pgAdmin is a GUI, not a console application - there is no stdin you could easily use. If you need stdin to stream your content, use psql, which is a console application - with the \copy meta-command of psql. If you have a file (which you obviously do), just use SQL ...


10

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


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

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

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.


9

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


9

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


9

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


8

Digging around the internet, I've seen that this is a pretty common problem. The common solution is to use the plain text format dump and feed it through iconv to correct the encoding. Here is more information about that.


8

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), ...


8

I don't think there is a global parameter to tweak in pgAdmin 1.16.* As an alternative you can open tables with: Tools -> View Data -> View Top 100 rows This is also available from the context menu of a table in the object browser. Once the data grid is open, a "Limit Bar" is available, where you can set a maximum for returned rows. I would ...


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

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


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