A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
4
votes
1answer
263 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 ...
1
vote
1answer
603 views

Parse hyphenated word to unsigned integer

When tested to_tsvector on 'someword-123' with english full text config, I got the following result: SELECT to_tsvector('someword-123'); to_tsvector tsvector -------------------- "'-123':2 '...
8
votes
1answer
2k views

Privileges required to install PostgreSQL

I am new to PostgreSQL Database. I need to install it in my machine. I am not administrator in my system. Can some one suggest, if Non-Administrator can install it. If there is any other way of ...
5
votes
1answer
3k views

Postgres scalability - What is the impact of connection pooling?

We had a question over on Server Fault that raised an interesting question: In light of the scalability improvements in Postgres 9.2 scalability improvements, is it better to use connection pooling ...
1
vote
1answer
1k views

How can I ensure that in a insert trigger a column gets filled before other column?

The bigger picture: tableX represents pipelines and tableY streets. A pipeline crosses/goes along many streets, and I want to know which street is crossed the most. First, I am segmentizing each ...
7
votes
1answer
6k views

How can I correctly choose maximum number of occurrences of a string while grouping by another field?

I am using Postgresql 9.0. I have the following fields in a table: id, name. id name 1 John 1 Mary 1 Mary 1 Mary 1 John 1 Mary 3 Paul 3 Paul 3 George ...
2
votes
2answers
1k views

Combining two similar SQL queries

I'm using the following SQL query twice with different values of limit. In my case, 40 and 100, though it doesn't really matter. My question is - can I combine these two queries into one query? It ...
0
votes
2answers
1k views

Merging multiple databases as separate schemas in PostgreSQL

I have to combine data from 4 different databases. Each having its own design. What i want to do is to Create a new database with my desired schema. and one schema per (to be merged) database Then ...
6
votes
2answers
5k views

Postgresql 9 speeding up indexed inserts (JPA)

I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
2
votes
1answer
4k views

Retrieving a fixed range of rows from Postgresql

I have a query: select qid,ansid,ans from table1 where askerid='something' This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
0
votes
1answer
478 views

Troubles with creating initial PostgreSQL base

I'm trying to create a new base /usr/pgsql/bin/initdb -D /var/lib/pgsql/data --locale=en_USand got the following: creating template1 database in /var/lib/pgsql/data/base/1 ... FATAL: could not ...
5
votes
1answer
191 views

Rebuild a table from history records

A small disaster happened to me on my PostgreSQL 8.4 database. I follow visitors to my webpage in two tables: History +---------------+-------------+ | whereseenlast | varchar(50) | | uniqid ...
2
votes
1answer
2k views

Postgres Incremental Backup (Continuous Archiving WAL)

I need to take incremental backup of Postgres DB. I need to capture (backup of) daily changes on a database. So that I can restore DB with any particular data I want. As per Continuous Archiving, I ...
8
votes
1answer
2k views

Performance impact of setting PostgreSQL to log all statements

PostgreSQL 9.2.2 on Windows 7 64-bit. The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting ...
5
votes
1answer
3k views

In Postgres, how do I cast character varying to an enum type?

I have a Postgres 8.4 table with columns that are of type "character varying", but I want to convert them to an enum type. When trying to alter the table ALTER TABLE logs ALTER COLUMN interface_type ...
1
vote
1answer
1k views

Depending on Foreign Key Constraints to avoid deletion

In PostgreSQL, I have a 2 tables with father/parent relationship. I wish to delete the father record if no records of child are associated with it (using after triggers) Currently, I have been doing ...
2
votes
1answer
2k views

Inherit audit columns and triggers

Background I have a database with dozens of tables, some of which should have audit columns. Problem I'd like to avoid the drudgery of creating audit columns manually, and write the update and ...
7
votes
1answer
4k views

Why/how did the behavior of extract(epoch…) from “timestamp without time zone” fields change between PostgreSQL 8.4 and 9.2?

Context: We currently run PostgreSQL 8.4 in production, and are testing 9.2 internally for a future upgrade. Some issues have emerged regarding dates. The servers that run 8.4 versus 9.2 are ...
0
votes
1answer
314 views

Can't connect to a PG database from MicroOLAP, but can connect from SSH?

I'm able to connect to my PGSQL database from SSHv2. But when I try to do the same thing on MicroOLAP Database Designer, I get this message: (Note: From my college PC, I can connect. But I can't ...
4
votes
2answers
311 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 ...
6
votes
2answers
4k views

Advantages of using PgBouncer vs HAProxy?

Are there any advantages for using one over the other? I'm only looking at options to hide the actual IP address of the master and slave servers from my processes, and not having to restart them if ...
2
votes
2answers
302 views

Teaching Slony replication to select slave nodes

There is a system with up to 256 nodes in db cluster. We need to replicate some data from one of them across to all others. But for every row to replicate there is a list of nodes where it must be ...
8
votes
1answer
5k views

What is the purpose of session pool_mode in pgbouncer?

Since in this mode pgbouncer creates outbound connection for each inbound connection it seems to me that pgbouncer doesn't work as pooler. It's just 1 to 1 mapping. Where am I wrong?
2
votes
2answers
252 views

Why is postgresql still consuming space on my other drive?

I am running postgreSQL 8.4 on Ubuntu 10.0.4. I recently needed to import a large amount of data (approx 300G) into a database. I purchased an additional drive, prepared it for use on my machine, and ...
1
vote
4answers
728 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
2
votes
2answers
1k views

Is it a good idea to move high-volume and highly-accessed tables to a separate database?

Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the ...
6
votes
1answer
6k views

Backup a database with a huge number of tables

Is there a way to backup a PostgreSQL database with a huge number of tables? An attempt with pg_dump for a database of about 28000 tables resulted in the following error message: pg_dump: WARNING: ...
3
votes
1answer
507 views

Postgres Backup queries on AWS after reading Postgres docs

I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance. So there are 2 ways to do it. Create a dump using pg_dump. File level backup with ...
7
votes
2answers
2k views

Continuous archiving: is it possible to use multiple machines?

Is it possible to use the archive_command to send to multiple machines? Is it just as simple as separating with a && since it is essentially just a command line? command1 && command2 ...
14
votes
1answer
23k 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 ---...
2
votes
1answer
261 views

GRANT privileges to table elements

What happens if I GRANT priveleges (for example SELECT) ON a table TO some group role? What privileges will the table's elements have in this case: indexes, sequences, columns? Should I explicitly ...
0
votes
2answers
3k views

Reloading .sql file from java into PostgreSQL - Windows

I am trying to reload a .sql script file into PostgreSQL from my sample Java application. Script is successfully executed from console as well as from pgAdmin tool. But from Java, it hangs... This ...
6
votes
2answers
815 views

What are my options for GUI based Administration & Management of PostgreSQL

I have been given the task of working with PostgreSQL 9.1. I have previously worked with Microsoft SQL, and had used the SQL Management Studio. I had used Oracle's SQL developer as well as TOAD when I ...
2
votes
1answer
20k views

PostgreSQL duplicate key violates unique constraint error

CentOS 6.2 64bit Server PostgreSQL 8.4.9 installed via yum I am seeing errors from time to time in PostgreSQL's logs about a table called events. The events table is used to stores information ...
2
votes
1answer
251 views

Audit tables in different databases

I am studying ways to implement audit tables to my PostgreSQL database and would like to know if it is possible to make a trigger for the data to be written to a table in a database other than the ...
2
votes
1answer
48 views

Insert command not showing schema details

I need to back up a table from a database schema.The table is located under kdb28_test_h and a schema named kref. I tried with the following code pg_dump --host=110.11.12.16 --port=5432 --username=...
8
votes
1answer
10k views

What is PostgreSQL database ownership

When creating a PostgreSQL database one can assign another role to be an owner of that database. What can the new owner do with this database? How is it compared to grants?
5
votes
2answers
1k views

Why might my Postgres table be growing?

I've got a table that grows under Postgres 9.1. It's a high-throughput table with lots of inserts and deletes, but it should generally remain between 1-2k in terms of number or records. Auto-vacuum is ...
1
vote
0answers
732 views

Finding the bottleneck by analyzing Munin graphs

We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
4
votes
1answer
2k views

Distinguish between schema and database

I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code: DECLARE v_tmp_host scan.location.alias%TYPE; v_ip_address character varying; BEGIN ...
1
vote
1answer
134 views

PostgreSQL custom domain with trigger

Is it possible to create custom domain / data type and associate some trigger with it, for example hash password? I will have for example password domain and if I set some value to this column, it ...
7
votes
1answer
2k views

What AWS instance to choose for write-heavy PostgreSQL database server

We are now having problems trouble to tackle the intensive traffic to our servers during peak hours; see this related question on tuning the DB. At the moment we are using a smaller cloud service for ...
5
votes
1answer
178 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
2answers
2k 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 ...
6
votes
1answer
470 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 ...
7
votes
1answer
3k views

Getting SQLSTATE in psql error messages

I have the following problem. SELECT * FROM tgvbn(); ERROR: function tgvbn() does not exist LINE 1: SELECT * FROM tgvbn(); ^ HINT: No function matches the given name and ...
2
votes
1answer
2k views

Archive database on Postgres

I have a table with more than 60mio records, where every month I have to add about 2-4 milions. It's not insert only table, I need to read from it and update recently added rows (status changes, etc.)....
18
votes
1answer
10k views

Database archive solutions

In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
5
votes
1answer
6k views

How does postgres use the schema search path when making indexes?

Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app. I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table ...
3
votes
1answer
361 views

Why does PostgreSQL aggregate result in the Index or Seq scan outputting all columns of the table?

Doing a query like this: SELECT fruit, avg(cost) FROM fruit_table GROUP BY fruit results in a query plan like this: Sort (cost=3713873.41..3713874.46 rows=420 width=16) Output: fruit, (avg(cost))...