Tag Info

Hot answers tagged

12

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


11

Or, to retrieve all rows, just press the blue data grid. The tooltip "View data in the selected object" is almost making it too easy. Try the button with the funnel next to it to retrieve only a selection (for big tables).


10

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.


8

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


7

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


6

Right click on the table and choose "View data" For a list of alternatives, check out the Postgres wiki: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools


6

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


5

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


5

pgadmin has it's own reverse-engineering functionality - it just examines the system tables like pg_class and pg_attribute to find the details. If what you're looking for is actually an easy way to show the CREATE statements for your objects that you can use from something else, you should look at pg_dump instead of pgadmin, it is much simpler. In ...


5

Other than not using features that cause errors, no, there is no workaround. Since in this case one of the features that causes an error appears to be listing tablespaces during startup, you're out of luck. Just compile a newer version of PgAdmin-III from sources or use the command-line psql client that comes with PostgreSQL 9.2. PostgreSQL's system ...


4

"I don't know how they got there in the first place" It could have happened as described here - although this generates an error on 8.4: If you create a table with any text type (i.e. text, varchar(10), etc.), then you can insert an invalid byte sequence into that field using octal escapes. For instance, if you have a UTF8-encoded database, ...


4

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.


4

You tell the COPY command to look for commas as delimiters (DELIMITERS ','), but there are no commas in your CSV. Use the 'text' format instead (it's the default, so you don't have to specify it) and do not specify a delimiter: The default is a tab character in text format. (source)


4

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


4

lock_timeout is a new parameter that appeared with PostgreSQL 9.3 Per the release notes: Add configuration variable lock_timeout to allow limiting how long a session will wait to acquire any one lock (Zoltán Böszörményi) That would indicate that pgadmin is using a PostgreSQL 9.3 pg_dump command to take the backup, instead of 9.0. This is ...


4

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.


3

You don't need to do this through the pg_hba.conf. Simply revoke the connect privilege on the database from that user. However by default public is granted the connect privilege. So you need to first revoke that: revoke connect on db1 from public; grant connect on db1 to dbuser; You need to run the revoke ... from public statement for all databases to ...


3

Since you installed multiple versions of PostgreSQL, there is a good chance your cluster is running on a different port than 5432. Additional clusters automatically use the next free port unless instructed otherwise. Like 5433, 5434 etc. Check your setting in postgresql.conf to find out and connect to the proper port. postgresql.conf is usually installed ...


3

The root nodes in the "Object browser" are actually connections, not servers. And a connection requires a user name. There is a bit of mis-labelling floating around, I have raised this issue on the developer mailing list before. However, you can just edit the properties of a connection before you open it. If you don't want to store your password for ...


3

DROP DATABASE is a very special command that cannot be undone. To my knowledge there is no way to drop a database inside a transaction. I quote the manual: DROP DATABASE cannot be executed inside a transaction block. Whenever you run two commands in a script, they are automatically wrapped into a transaction. You can explicitly begin and commit ...


3

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


3

There is no explicit switch to just export functions. I did some research since it's a nice question. I can think of some ways to do this: Using pgAdmin, you get the code that makes up the function when you right-click on the function, and choose "Scripts->Ceate". You could copy&paste the contens and put it into a sql file that you can import like ...


3

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


3

You need to enter the password of the database user, not the OS user. The "root password" is not applicable. Do you know the password of the database user postgres? Most systems are set up allowing peer authentication for local connections, meaning a system user can log into the database with a database user of the same name without password. localhost is ...


3

You could setup the ssh connection and tunnel outside pgAdmin and just let pgAdmin connect to the localport. On windows you could use putty for this on unixes you can use ssh If you use 15432 as localport then you can let pgAdmin connect to localhost with port 15432 and you leave the SSH tunnel option in pgAdmin OFF!


3

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


3

The sequences are designed like that, the are not supposed to produce contiguous numbers. However, you can reset them before (re)trying the COPY with SELECT setval('sequence_name', (SELECT max(id) FROM your_table));


3

The basic misunderstanding: "localhost" is not a local connection. Your "trust" line in pg_hba.conf is not applicable for your connection: local all all trust That only applies for connections via Unix-domain socket. Best choice for you is to connect via this route. Per pgAdmin documentation: The host is the IP address of the machine to contact, ...


2

two options: Use the "datatype" SERIAL or create a sequence and use this sequence as a default value for your integer: CREATE SEQUENCE your_seq; CREATE TABLE foo( id int default nextval('your_seq'::regclass), other_column TEXT ); INSERT INTO foo(other_column) VALUES ('bar') RETURNING *;


2

The most up-to-date version is always available with the most-up-to date version of postgres. So the coming 1.14 of pgAdmin support 9.1 with it's features, 1.12 -> 9.0, 1.10 -> >8.4. I noticed that pgAdmin will disable (or disallow you to use) certain features that do not support your installed pg version. Sidenote: I made bad experiences with using the ...



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