6,979 reputation
31439
bio website
location Berlin, Germany
age 38
visits member for 2 years, 10 months
seen 2 days ago

Nov
18
comment copy data from public schema to another schema in postgresql
If you do pg_restore -l -f db/my_dump.backup, you'll see that the objects are dumped, meaning that the restore step does not do what you expect from it. If you are doing this regularly, the best way (in my view) is to store the database object definitions under version control, then roll them out on demand to a new schema with a few lines of shell script.
Nov
17
comment How to enable implicit casts in PostgreSQL >=9.2?
@CraigRinger I think this is more or less the full answer one can give here.
Nov
17
comment pgAgent Job's “last result” is continuously “running”
Could you show what the job is supposed to do, together with the table structure?
Nov
14
comment Multiple databases with medium tables or Single database with huge tables
See dba.stackexchange.com/questions/82558/… We actually have some DBs with up to 1200 allowed connections but it is far from being optimal.
Nov
14
comment Multiple databases with medium tables or Single database with huge tables
Now I've checked the server options you linked. As they offer SSDs for every size, it makes life a bit easier as disk reads are much cheaper than with spinning disks. I'd suggest putting as many of you DBs on the cheapest option (where the relative disk size is the biggest) as you can, and run some benchmarks there.
Nov
14
comment Multiple databases with medium tables or Single database with huge tables
One of the biggest concerns I see with solution 1 is the number of connections. If you have 8000 shops and 2-3 users logged in from each, it means something around 20k connections. This is well beyond the capabilities of a simple PostgreSQL installation. Even using some connection pooling, this would be more than tricky. The size of the machine usually depends on the size of the DB. If the whole DB fits into memory, you are in a very comfortable situation. Could you possibly tell how big the average DB is (in disk size)?
Nov
13
comment Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?
You can also keep the common columns in a single table and set up the specific types as tables referencing this central one via foreign keys.
Nov
13
comment Problem using pg_dump in Windows 7
Have you tried doing this in two steps, first writing the pg_dump output into a file and then feeding it to psql? And an actual error message (which may even show the row number it originates from) would be more than handy.
Nov
12
comment How to manipulate join order of PostgreSQL optimizer?
Why would you do that? Without modifying the source code, you cannot achieve this.
Nov
12
comment Trigger or Stored Procedure?
While I agree with you, I have to add a con: with many of these triggers it will be very hard to follow what happens on a simple INSERT. I used to spend much time debugging funny behaviour, partly induced by my denormalization triggers.
Nov
12
comment EXECUTE within function not using index?
@francs Why would it be any different in an other client?
Nov
11
comment Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?
You mean bytes?
Nov
11
revised Postgresql doesn't accept replication connection
added 82 characters in body
Nov
11
comment Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?
There is no mechanism that will do this for you behind the scenes. However, you can normalize these data into two separate tables (purchased_name and purchased_description, for example) and have a foreign key in the original table referencing these two.
Nov
10
comment Postgresql cluster over nfs
Yeah, exactly that's the idea behind replication - to have a replica.
Nov
10
revised PostgreSQL roles versus users, grant permissions
added 266 characters in body
Nov
10
comment Does Postgres shutdown gracefully when I restart my Mac?
From the PostgreSQL logs, you will see how it is being shut down. Otherwise yes, it should close PostgreSQL nicely.
Nov
10
revised PostgreSQL: Creation date of a table
added 6 characters in body
Nov
8
comment Are PostgreSQL dynamic arrays more wasteful than plain text?
I think the most important thing here is the last sentence.
Nov
8
comment Error: /var/lib/postgresql/9.3/main is not accessible or does not exist
And what was the weird error message? How did you take the dump? Is your installation in the directory in the title?