6,659 reputation
31436
bio website
location Berlin, Germany
age 38
visits member for 2 years, 7 months
seen 1 hour ago

Jun
16
comment Using the correct postgres server
It's not very likely, 8.4 usually creates its files in a .../8.4/... directory. Which version did you install recently?
Jun
16
comment Using the correct postgres server
Hm. That means that you have to find out where the old data files are.
Jun
16
comment Postgres reported table / db size varies wildly from dump size
This is definitely not true. It very much depends on the data. I've done a short test on one of my toy databases, it reports 8554 kB in psql, the text format dump is 2279 kB.
Jun
16
comment Using the correct postgres server
You have to su - postgres and start the server like that. The root user is not allowed to do that for security reasons.
Jun
16
comment Postgres reported table / db size varies wildly from dump size
Your question related to LOBs is answered here: stackoverflow.com/questions/18636850/…
Jun
16
comment Implementing Permissions Database structure on a PHP site to restrict the Access in Multiple levels
The PostgreSQL role system is currently unable to handle row-level permissions (that, if I understood correctly, is what the OP tries to implement).
Jun
5
comment Better way to restore to existing pg database
Use the -c and -C options of pg_restore. At the same time, I am wondering why you need to restart the server...
Jun
5
comment How can I get PostgreSQL to not advance sequences when COPY fails?
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 table));
Jun
3
comment trigger_file doesn't work
Do the logs tell you something happening when you create the file?
Jun
3
comment Recursive calls in stored procedure and temporary tables
How about moving the 'non-recursive' functionality (including the temp table creation) out to an other function and call the recursive magic from it? At the same time, some indentation in your code could help readers understanding what you try to achieve. At at still the same time, are you sure that floating point variables are good for this sort of calculation?
Jun
3
comment Organizing Database Objects in a Database that are not part of the systems original Database Objects?
On one hand, a sensible naming convention can solve part of your problem. A more sophisticated solution is to use schemas: they are a good way to organize your stuff and also make you able to set access rights at this level.
Jun
2
comment Change column type from varchar to text in all tables at once
In my experience, all varchar(255) columns are of this type because of false assumptions about the performance gained this way or copypaste-like development behaviour. Usually, over 32 characters or so, limits are not really meaningful.
Jun
1
comment How un-clustered is a CLUSTER USING table in PostgreSQL
I edited back your title as psql is a client for PostgreSQL, it has no tables at all.
Jun
1
comment Optimize a query on two big tables
Nice one! I'd add that one could give better names to these tables. Changing the names could be rather painful, though.
May
28
comment How do I decompose ctid into page and row numbers?
@ErwinBrandstetter: sure :) My builtin orthography processor keeps failing occasionally.
May
27
comment How do I decompose ctid into page and row numbers?
IIRC it is a vector type and we don't have accessor methods on these. I am not sure if you can do it from a C function. Craif will tell for sure :)
May
23
comment COPY TO .csv and add quotes to header in PostgreSQL
Why do you need such a thing? :) Otherwise, I am unaware of such a feature. You could add those quotes with sed, for example, after the file is created.
May
21
comment Restricting Insert and Update to functions
@user38729 then please mark the answer as accepted. Thanks!
May
20
comment How can I figure out what service is trying to connect as root?
In our case, using CSV format logging, the IP and port of origin can be seen from the log. On the other hand, when trying to connect with a nonexistent user, I get psql: FATAL: password authentication failed for user "elek", not the error you show. This may be of some help: stackoverflow.com/questions/16973018/…
May
20
comment Postgres live backup
Then why a hot standby and a plain failover are not an option?