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 ...
0
votes
0answers
31 views
Create Log file for insert,update and delete records and retrive the log details
How to create/update log file for each modifications(insert,update,delete) in my database. i cant find any feasible methods to achieve so. can anybody know how to implement this.? if so then please ...
0
votes
1answer
52 views
Retrieving all PK and FK with pgadmin
I have a big database that I need to extract all primary keys and foreign keys from each table.
I have pgAdmin III.
Is there a way to do this automatically and not go over each table manually?
2
votes
3answers
68 views
get account activity in postgresql
I have a old DB with bunch of accounts. One of them is used for sure but for 5 of them or so there is no information.
Is there any info in the system table in Postgresql when was the last login or ...
1
vote
4answers
63 views
How to determine if a postgres database needs to be vaccumed?
Given a PostgreSQL database what command can be used to determine if the database needs to vacuumed?
UPDATE I am working with client who is evaluating postgres but I am not a DBA, I just happen to ...
6
votes
1answer
85 views
What is a good way to determine the performance I should expect from a PostgreSQL server?
Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
0
votes
1answer
115 views
Postgresql: Difference between pg_dump and backup/restore?
Don't they essentially do the same thing by storing all the data in a file and dumping it into another database? Is it that pg_dump just adds the data on to whatever is already there and restore ...
6
votes
6answers
395 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
1
vote
0answers
77 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
66 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
94 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
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 ...
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
88 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
294 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
154 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
144 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
27 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
62 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
319 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
107 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
112 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
74 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
345 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
364 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
71 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
84 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
58 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
151 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
119 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
26 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
262 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
405 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, ...
