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


9

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


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

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


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


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

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

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

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

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


3

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


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

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

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

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


3

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.


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

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


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


2

Backup without password If you run the script as OS user postgres it should not request a password in a standard setup, because password-less peer access (or ident on older versions) is enabled in pg_hba.conf for the postgres user. So make it a cronjob of postgres if you can. Or enable password-less access for the OS user running the job. You can do that ...


2

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


2

I found the answer to my problem. The pgadmin3 tool won't let you specify both a server and a database. The server itself HAS a default database and when you select it with the --server option, it won't let you pick a database. An error message would have been nice! The 'defiant' server I made has in itself a default connection to a database, you have to ...


2

Thanks to @araqnid on Stack Overflow for the answer here. Quoted Verbatim from there: Open the "Properties" of the Heroku server in pgAdminIII and change the "Maintenance DB" value to be the name of the database you want to connect to. The default setup is suitable for DBAs et al who can connect to any database on the server, but apparently that isn't true ...


2

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


2

Look into the module auto-explain. We had a very similar question some time ago, it should be of help: Postgres query plan of a UDF invocation written in pgpsql


2

If started from the same SQL window, then no - you will always get the latest result set (or nothing, if the last command returns no rows, like in the case of COMMIT). Not sure if this is OK for you, but you can open as many query windows as you want and so have as many result sets. (The above means that I'm not aware any such plugins.)



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