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 ...
1
vote
0answers
76 views
SOLVED: Postgres 8.4.5: Bad query plan vs Good query plan when changing date range on large data table
SOLVED.
I was able to resolve this issue by altering the time.date column to allow 1000 stats: ALTER TABLE time ALTER COLUMN date SET STATISTICS 1000;
Now the queries are running very fast, down to ...
2
votes
2answers
93 views
Permission denied in Postgres
I added the user myuserto Postgres.
Then, through the PgAdminIII GUI I added the database mydatabase and I restored from a backup file. So, the owner of mydatabase is the "administator" user ...
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 ...
1
vote
3answers
63 views
Where are PostgreSQL databases stored on my computer?
So this might be a stupid question, but I'm really new to this.
So I'm making a Postgres database on the postgres server, that I started up with this command:
sudo -u postgres psql
And then I did ...
4
votes
0answers
68 views
Does PgBouncer create a separate pool for every database config entry?
If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one? I would think so, but have not been able to confirm this.
For example, ...
1
vote
0answers
31 views
postgresql 8.3 possible checkpoint and memory misconfiguration
I am using postgresql 8.3 in a read/write heavy application where data are being written to the database from a background process while they are also being read/written from a web application.
At ...
1
vote
1answer
37 views
Postgres Automated Install
Is it possible to automatically create a database after a postgres one click install (from Enterprise DB)?
5
votes
1answer
91 views
How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?
Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling.
What is the best pattern to use for this kind of set up? Should I have one-engine-per-process, ...
4
votes
2answers
83 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 ...
2
votes
0answers
72 views
How to properly configure pgBouncer transaction level pooling?
What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
2
votes
2answers
110 views
QGIS PostGIS Authentication Failure with “trust” option
I'm trying to setup a PostGIS db using PostGreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, ...
1
vote
0answers
84 views
Remote connection to PostgresSQL in Windows 2008 Server is prompting the error below
The error:
"Unable to read data from the transport connection. an existing connection was forcibly
closed by the remote host."
The conditions:
1) I have the following connection string:
...
4
votes
0answers
37 views
Postgres TupleTable like data structure
I'm writing my own Group by operator (non-hashed implementation) and I'm currently looking on a good data structure to store the result groups. There are two requirement in my mind: the table need to ...
2
votes
2answers
279 views
How do I remove duplicate records in a join table in PostgreSQL?
I have a table that has a schema like this:
create_table "questions_tags", :id => false, :force => true do |t|
t.integer "question_id"
t.integer "tag_id"
end
...
1
vote
0answers
31 views
After streaming replication has failed, how to get it back again?
I have a similar problem to this: Replication has failed; how to get going on again?
Essentially my slave failed, and now complains "requested WAL segment 0000000100000135000000E4 has already been ...
1
vote
0answers
153 views
Is there a Postgres admin GUI that can execute a master file containing multiple sql files
I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file.
example: ...
0
votes
0answers
140 views
Install PostgreSQL 9.2 on Windows using WIN1252 encoding.
I had installed PostgreSQL 9.2 earlier and it always installed with the encoding being WIN1252 (the default database was WIN1252). I then some time ago reinstalled it with the encoding being UTF8 (I ...
3
votes
3answers
26 views
how to chain postgres RULEs?
I have implemented data denormalization strategy using postgresql RULEs. I picked rules instead of triggers for performance reasons.
Schema is structured like this:
Application has many clients
...
2
votes
3answers
124 views
How can I store a pdf in PostgreSQL
I have to store .pdf files in a table.
I have a table, state, with columns:
id_state,
name,
pdffile (bytea)
I want to store the pdf files in the pdffile column.
How can I do this?
1
vote
0answers
66 views
Does spgist extension in PostgreSQL 9.2.3 support float or int array?
SP-GiST is an abbreviation for space-partitioned GiST in PostgreSQL 9.2.3. It can be used to find the similar tuple by creating the sp-gist index on the column.
e.g:
create index pt_spgist_idx on ...
1
vote
1answer
44 views
Script to get duration
I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
1
vote
1answer
61 views
Postgres copy data with \xYY as plain string instead of interpreting as encoded string
I have a log file full of URLs, generated by Bro IDS.
When Bro logs a URL with non-ascii characters in it, it inserts \xYY where YY is the hexadecimal character code. Also, some URLs contain "\x".
...
1
vote
1answer
82 views
How to Reorganize Microsoft Access Table
I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of ...
2
votes
1answer
49 views
What fillfactor for caching table?
I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
3
votes
2answers
310 views
PostgreSQL Sequential Scan instead of Index Scan Why?
Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
3
votes
3answers
106 views
What is the best way to store X509 certificate in PostgreSQL database?
I'm working on web authenticate system where users will digitally sign random tokens and this will be checked with X509 certificates stored on the server.
Therefore I have to store several X509 ...
0
votes
1answer
71 views
Postgresql Run 'Jobs' At Specific Times
I have a normal problem to anyone that can't trust on applications to do their 'work/ AKA be online' and needs a DB Stored Procedure to run every minute .
Scenario :
PostgreSQL 9.2
Need To Run A ...
1
vote
2answers
168 views
How should I tune Postgresql for 20 GB of RAM?
I've been fortunate enough to have the use of a 20GB Linode instance running Ubuntu 64 bit. I want to try to optimize PostGres for this service, but I don't know what I should prioritize changing.
I ...
0
votes
1answer
111 views
UPPER Case issue postgreSQL when importing from text file
I have ruby and rails app, and I have cron task to download text file and import it to the database using
\copy hotels FROM 'db/ActivePropertyList.txt' DELIMITER '|'
There is a header field which ...
1
vote
1answer
73 views
PostgreSQL no relations found
I am running ruby and rails app with postgresql database, and I want to do batch import from text file to the database.
I've configured the database and if I run psql and \l to list all databases, I ...
0
votes
3answers
334 views
Export Postgres table on remote server to CSV file on local machine?
I have access to DB on remote server where I can do only select command,
So, I can execute:
COPY products TO '/tmp/products.csv' DELIMITER ',';
But on that server I don't have permission to ...
2
votes
0answers
37 views
What is the equivalent of a “Certificate of Destruction” when using a cloud-hosted database?
We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide ...
2
votes
2answers
346 views
PostgreSQL user can not connect to server after changing password
I've met this with 4 roles I created:
After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more.
pgAdmin III show error message:
An error has occurred:
...
1
vote
0answers
69 views
Can PostgreSQL's COPY create CSV with Locale-specific number formatting?
I want to create a CSV with PostgreSQL 9.1 COPY that directly opens correctly with a double-click in crazy German Excel 2010. Using ";"-Delimiter is easy.
The encoding is solved, but Excel only ...
4
votes
1answer
81 views
Materialized View PostgreSQL
Is there any possibility of having built-in materialized views in PostgreSQL ?
I don't want Gardner version or somehthing like physical table populated by triggers.
I've read that was/is something ...
0
votes
0answers
53 views
Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability
I'm in the process of configuring postgresql 9.2 streaming replication with Redhat Clustering Suite for high availability.
I have 1 master and 1 hot standby configured to replicate the data from ...
0
votes
0answers
15 views
Checking the integrity of two database which are replica of each other
We have a database running on postgres. We replicate the data values in cloud in a customized way. Now we are looking for checking the integrity of certain tables of two databases. What are possible ...
1
vote
1answer
146 views
Tuning Postgres config for new Database Server
I have finally gotten a Purpose Built Database machine for a project i'm working on.
The server is a 4 Core Xeon with 64GB of Ram and a Raid10 of 10krpm drives.
I have just got the database moved ...
5
votes
1answer
118 views
How to improve performance on PostgreSQL when using multiple concurrent processes?
Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...
0
votes
0answers
23 views
Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs
I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs.
All the primary keys are type serial (auto incrementing integer) and all have the name in the ...
3
votes
1answer
257 views
How many queries per second is my Postgres executing?
How can I tell how many queries per second my Postgres database is executing?
5
votes
1answer
403 views
PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly
We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
-3
votes
2answers
170 views
Versioning data in Databases [closed]
Is there any ready mechanism in Oracle, SQL Server, PostgreSql or OR framework (C#), which can version information in a database? By versioning informartion I mean that e.g. I have table Person (id, ...
1
vote
1answer
60 views
How to view the current settings of Autovacuum in Postgres?
I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration.
Postgres 9.1 is the version I'm most interested in.
0
votes
1answer
64 views
Difference between return next and return record
What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
2
votes
1answer
164 views
returning set of records in stored procedure
I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures:
create or ...
1
vote
0answers
126 views
How to calculate running total based on dynamic values?
I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
1
vote
1answer
279 views
Declare variable of table type in PL/pgSQL
I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like:
q1 = select * from foo;
q2 = select * from bar;
...
0
votes
0answers
132 views
Postgres error ( XX000: invalid memory alloc request size 4294967290 )
I'm getting the following error in my postgres 8.4 database:
XX000: invalid memory alloc request size 4294967290
Operating System : Windows 7 64 bit
Memory : 8GB
Database Version: Postgres 8.4 ...
2
votes
0answers
174 views
“custom archiver out of memory” error when restoring large DB using pg_restore
I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore. The Heroku DB is using 9.1.7 and I have had the same result ...
