PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...
4
votes
1answer
75 views
Can I activate PITR after the DB has been used
My Postgres-Server is running for quite a while and contains probably 4-5 GB of data.
Backups are done using dumps, and this works ok. Though not most efficient, it just works.
Due to future growth ...
4
votes
1answer
45 views
Is there better way to 'touch' a row than performing a dummy update?
I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update:
...
4
votes
1answer
59 views
Safe way to stop/abort REINDEX
I am running a REINDEX but it takes long time and still working. The application using the DB is not responding right now. Is there a safe way to stop it and do it another time?
PostgreSQL version is ...
4
votes
1answer
59 views
Postgresql, write archive file over mount, secure against simultaneous access?
I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share.
I just use the basic command, given as an example in the postgresql.conf
test ! -f /mnt/server/archivedir/%f && cp ...
4
votes
1answer
1k views
How to use aes-encryption in PostgreSQL?
I tried aes-encryption by using following statement:
SELECT encrypt('test', 'key', 'aes');
which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
4
votes
1answer
137 views
PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client?
I am using Certificate Authentication for clients on a PostgreSQL 9.1 database server. Several certificate common names are mapped to a single database user in pg_ident.conf.
I do not want to create ...
4
votes
1answer
223 views
Upgrade Postgres without doing a pg_dumpall
I'm upgrading from postgres 8.4 to 9.0 and looking at the manual it looks like this is my only choice:
pg_dumpall -p 5432 | psql -d postgres -p 6543
Which means I'll need to stop any writes to the ...
4
votes
1answer
1k views
How to get actually allocated tablespace size by each postgres database?
Postgres tablespace is cluster-wide. Therefore, how do I caculated how much space allocated by each database in postgres tablespace ?
4
votes
1answer
54 views
Want to add a “variable” to a table
I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to ...
4
votes
2answers
138 views
Deadlock during Slony uninstall and concurrent access of DB
I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process.
I was trying to uninstall node from a cluster, as per slony's documents this requires access ...
4
votes
2answers
457 views
Postgresql 9.0 adding new database to streaming replication
postgresql 9.0 streaming replication + hot standby is running. Now i need to add one more database to current cluster. What is the best way of doing this? I have a backup file about 20GB. should i ...
4
votes
1answer
349 views
Is the PostgreSQL 'connection limit' shared across all users in a role?
I have created a role that many users can share:
> create role foo_read_only connection limit 5;
I have then assigned permissions to that role:
> grant select on table1 to foo_read_only;
...
4
votes
3answers
47 views
PostgreSQL group roles
This is not a "group by" question. I am struggling with permissions in postgres because I apparently don't understand how group roles work. Searching for this on SO has proven difficult because I get ...
4
votes
1answer
85 views
How can I benchmark a PostgreSQL query?
I want to benchmark a query containing an user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark?
I know that timing can be turned on with \timing ...
4
votes
1answer
29 views
How to view the full, flattened query underlying a Postgresql view?
If I have a view on a view on a view (etc.), is there a way to show the full query that this expands/flattens to?
4
votes
1answer
90 views
Transactions, references and how to enforce double entry bookkeeping? (PG)
Double entry bookkeeping is
a set of rules for recording financial information in a financial
accounting system in which every transaction or event changes at least
two different nominal ...
4
votes
2answers
128 views
What happens to the index of a primary key after a DROP CONSTRAINT?
I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the ...
4
votes
1answer
149 views
Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?
I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
4
votes
1answer
195 views
Is it possible to wrap aggregate functions in Postgres?
Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is ...
4
votes
1answer
594 views
How to turn off header only in psql (postgresql)
I'm using PostgreSQL 9.1.X
I am trying to build psql script to print results without a header but including a footer.
http://www.postgresql.org/docs/9.1/static/app-psql.html
From the document ...
4
votes
1answer
181 views
Can you make SSL and SSL-only (hostssl) connections to PGPool?
The new version of PGPool (3.2) has an "SSL" section in the conf file, and directions on how to use it in the manual. However, pool_hba.conf specifically says that PGPool doesn't support SSL (i.e. ...
4
votes
2answers
2k views
Connecting pgAdmin3 to Postgres on Heroku
I'm running a Heroku Postgres database (Database) in assosciation with a Django app. The docs that refer to the add-on are here. I thought this would be a worthwhile question for the community ...
4
votes
1answer
2k views
PostgreSQL Database Data File Integrity Check
When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption?
In the ...
4
votes
1answer
285 views
Single node parallelism with PL/Proxy?
I have seen some suggest that single node parallelism is possible, but all of the material I have found so far deals with parallelizing across nodes. I am interested in getting large static queries to ...
4
votes
1answer
587 views
PostgreSQL: Corrupt primary key, inconsistent table
While recovering from a cloud failure, I found that some tables on a PostgreSQL database are behaving strangely. These tables are indexed using a primary key, but a pg_dump yielded duplicate fields, ...
4
votes
1answer
237 views
PostgreSQL exit status when running a file
When running PostgreSQL with a single SQL command error codes are returned as expected:
% psql -c "SELECT * FROM AWDASDASDASDAS" my_db
ERROR: relation "awdasdasdasdas" does not exist
LINE 1: SELECT ...
4
votes
1answer
621 views
PostgreSQL on Ubuntu - Using conf files for recovery
I am using PostgreSQL 8.4 on Ubuntu LTS 10.04.02 server version. I use pg_standby to restore the data and for database recovery created a recovery.conf and placed it on /etc/postgresql/8.4/main where ...
4
votes
1answer
286 views
PostgreSQL relation size does not sum up
I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow:
=> SELECT ...
4
votes
1answer
18 views
How to simplify a nested SELECT with PostgreSQL arrays?
I'm trying to speed up and simplify a SQL query against an imported OpenStreetMap database (OSM). The database is stored in a PostgreSQL 9.2.4 server.
This OSM import features two particular tables, ...
4
votes
1answer
89 views
How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?
According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some ...
4
votes
2answers
120 views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
4
votes
1answer
66 views
How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
4
votes
1answer
78 views
After Rackspace server creation, PostgreSQL query planner doesn't work as expected
We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
4
votes
2answers
85 views
How to properly configure PostgreSQL RAM usage?
Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
4
votes
1answer
117 views
Storing logs on per day basis in PostgreSQL
Requirements here are to store historical logs.
For the sake of simplicity I'll assume this sample scenario where we are selling some products on our website and we need to keep records of sales of ...
4
votes
1answer
174 views
PostgreSQL pg_stat_activity shows COMMIT
We recently replaced our database server with an upgraded machine with 4 x quad core CPUs and 32Gb of ram. We also repurposed our old box to serve as a slave with streaming replication. Both boxes are ...
4
votes
1answer
137 views
PostgreSQL: Recommendation size for shared_buffers
I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago.
I am looking for help about the proper memory values for the configuration file.
Currently, I ...
4
votes
1answer
166 views
What is actually happening when postgresql discovers the trigger file and promotes it self from slave to master?
I have a streaming replication setup between two postgres servers (master: server A, slave: server B). I'm wondering what's actually happening under the hood when I touch the trigger file and the ...
4
votes
1answer
120 views
Do isolation levels only apply to SELECTS and not UPDATES? [closed]
Scenario that might demonstrate different behavior for SELECTS depending on the isolation level:
1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete
2) 0:02 Thread B ...
4
votes
1answer
220 views
How do I resolve Postgresql error, 'no collation was derived for column “foo” with collatable type citext'?
Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error:
ERROR: no collation was derived for column "vin" with collatable type citext
...
4
votes
2answers
1k views
Is there a means to set the owner of all objects in a PostgreSQL database at the same time?
Modify OWNER on all tables simultaneously in Postgresql describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ...
4
votes
1answer
44 views
LISTEN / NOTIFY privileges
I have a single postgres database, with two users; Alice and Bob.
I would like to be able to do a NOTIFY alice_channel 'sensitive data' without Bob being able to sneakily LISTEN in just by guessing ...
4
votes
1answer
3k views
PostgreSQL: permission denied for relation
I'm a bit confused about setting permissions in PostgreSQL.
I have these roles:
List of roles
Role name | Attributes | Member of
...
4
votes
1answer
119 views
Troubleshooting Postgresql spatial query failure. Next steps?
We have a set of spatial queries that are failing and I'm struggling with troubleshooting them. I suspect that we're running into some bug, but I'd like to nail things down a bit better so as to be ...
4
votes
1answer
1k views
Install PostGIS v2 for PostgreSQL v9.2
I have installed the latest stable version of PostgreSQL v9.2 and I want to install the PostGIS v2 extension.
I've been searching for it for 3 days now, however can't find it.
I did however find ...
4
votes
1answer
225 views
How large should my pool size be? And should I use pgbouncer?
I have a Rails application with 4 Unicorn workers. How do I determine what postgresql database pool size I should use?
And if I have 6 applications connecting to this postgres database, would that be ...
4
votes
1answer
285 views
PostgreSQL data type text vs varchar without length
In PostgreSQL you can create a column with data type character varying (without length precision) or text like this:
ALTER TABLE test ADD COLUMN c1 varchar;
ALTER TABLE test ADD COLUMN c2 text;
Is ...
4
votes
1answer
142 views
timelines and history files PostgreSQL 9.1
What is the purpose of timelines, history files, and the setting 'recovery_target_timeline' in recovery.conf?
The vague understanding I have from the PostgreSQL 9.1 documentation is when the slave ...
4
votes
1answer
543 views
How to calculate cache misses for PostgreSQL
I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
4
votes
1answer
1k views
PostgreSQL failover and replication
I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details.
I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I ...
