PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

5
votes
1answer
162 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 ...
-1
votes
1answer
1k views

Postgresql could not find pg_xlog [closed]

I'm trying to mount the WAL to a different disk. I stopped the server, and did the following: (I'm using Fabric) # Move pg_xlog to wals/path.dev/ dest_dir = "/home/ec2-user/%s/wals/%s" % ...
0
votes
2answers
447 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 ...
0
votes
1answer
1k 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 ...
13
votes
2answers
3k views

What is the modern way to partition PostgreSQL across machines, when the data is “naturally partitionable”

After several years of dwelling into the "NoSQL" space, now I have a problem that is quite "relational" in its nature. Today I see data stores with quite different eyes than before. Things like Riak ...
2
votes
1answer
1k 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 ...
4
votes
1answer
615 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 ...
3
votes
1answer
445 views

pg_dump freezes on windows service application

I have a system running over Tomcat which executes a backup batch job every day, it calls pg_dump.exe from Java and stores its output to an SQL file. When I'm executing Tomcat from the shell, it ...
0
votes
1answer
285 views

Use data in a tablespace to populate another postgres database instance without actually inserting

I have a postgres database (version 8.4) with many tables. Some tables are huge in size, but the data is static (common for any instance). I have stored such tables in a separate tablespace. Is there ...
1
vote
1answer
350 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
880 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 ...
4
votes
1answer
3k views

How do I list all tables in all schemas owned by the current user in Postgresql?

I can list all tables in all schemas using > \dt *.* but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by ...
5
votes
1answer
270 views

Determining which isolation level is appropriate

This is a homework question. For the following transactions state the isolation level that will maximize throughput without lowering the integrity of the database. Explain the answer. ...
0
votes
1answer
233 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
1k views

How do I set and get custom database variables?

Using PGAdmin III I can right-click on a database, navigate to the Variables tab, and put a variable name-value property on the database itself. Is there a way to customize these? I saw an ...
5
votes
1answer
2k 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 ...
6
votes
1answer
4k views

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy ...
1
vote
2answers
753 views

list tables that are missing primary/foreign key in postgresql database

I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing primary and foreign keys? PostgreSQL 8.4 and 9.x versions
4
votes
1answer
2k views

query to identify all data types used in postgresql database tables

I don't want to know all data types, just all data types used in my database. Can this information be queried? PostgreSQL 8.4 and 9.x versions I currently need to know all data types for over 200 ...
5
votes
2answers
2k 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 ...
4
votes
1answer
152 views

PostgreSQL replication for archiving

I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice. I currently use ...
0
votes
1answer
295 views

Any advantages if we use multiple database instead of only one in PostgreSQL?

I would like to get the suggestion for my usecase. We have a database in our production machine.We are doing continuous writes in that database since we are doing crawling,parsing and it is being ...
2
votes
1answer
265 views

Pattern similarity in time-series [closed]

I have a PostgreSQL database and my data-set include time-series of temperature for two stations. I would like to look their pattern's similarity in similar time sequences. For station number 1: ...
4
votes
1answer
856 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
1answer
333 views

Removing duplicate entries for each hour

I recently asked the same question about MySQL, which has since been migrated to Postgres. Old question. To sum it up: I have a table with an id (type UUID), a timestamp, and some text (row3). How ...
4
votes
2answers
595 views

ON DELETE CASCADE on pg_class in PostgreSQL does not work

I added a foreign key on my table which references pg_class. ALTER TABLE summary_table ADD FOREIGN KEY (table_oid_column) REFERENCES pg_class (oid) ON DELETE CASCADE; I have a summary table which ...
2
votes
2answers
4k views

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...
5
votes
1answer
2k 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
319 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 ...
8
votes
1answer
12k 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
4answers
5k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes ...
2
votes
1answer
1k views

How to switch master server and warm standby (with streaming replication) in PostgreSQL?

I would like to know what is the way to switch the role of 2 servers when using warm standby? I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with ...
0
votes
1answer
2k views

Error restoring Postgres DB

I'm trying to do a pg_restore on some SQL files. Here's the command I used when I dumped from the other database: pg_dump -f /home/mctools/public_html/cp/upload/manufacturers.sql \ -Fc -i -v ...
2
votes
1answer
190 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 ...
2
votes
1answer
3k views

Logging to a file in PostgreSQL windows service

I need an alternative for running the following command: C:\xxx\pgsql\bin\pg_ctl" -D "C:\xxx\pgsql\data" -l "C:\yyy\log\pgsql.log" start. This way the server starts and logs to C:\yyy\log\pgsql.log. ...
0
votes
2answers
1k 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 ...
2
votes
1answer
136 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 ...
6
votes
3answers
676 views

How can I export a subset of tabledata from a prodution database into my local testing database?

We have a relatively big production postgres based db: ~20GB. The PostgreSQL database is hosted on heroku. I would like to copy a small subset of the table data to my local database so I can run ...
0
votes
2answers
10k views

PostgreSQL - insert/update violates foreign key constraints

I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't able to insert data into the Table3. See the code below : DROP TABLE Table1 ...
2
votes
1answer
46 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 ...
4
votes
1answer
2k 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?
1
vote
0answers
404 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 ...
1
vote
1answer
4k 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 ...
0
votes
1answer
99 views

How to determine why connection creation lasts so long?

On one of my servers (Windows 2008 Server), creating connection in Java to PostgreSQL server takes from 1,5 to 4 seconds! I have no clue how to start checking what can be wrong. I tried different ...
1
vote
1answer
2k views

restore compressed dump query in postgresql asks password

I'm using ubuntu OS and I took backup using sudo -u postgres pg_dump dbname | gzip > filename.gz in the file path as... /home/administrator/Documents/DB_Backup# sudo -u postgres pg_dump dbname ...
3
votes
2answers
232 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
1answer
73 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 ...
3
votes
1answer
926 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 ...
6
votes
1answer
4k views

Moving postgresql data to different drive

I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
4
votes
1answer
160 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 ...