Tagged Questions

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 ...

learn more… | top users | synonyms (1)

0
votes
0answers
50 views

postgres-xc - ERROR: object already defined [closed]

I am configuring two data nodes and a coordinator on postgres-xc. I get eh following error: ERROR: PGXC Node datanode1: object already defined CREATE NODE pgxc_pool_reload ------------------ t (1 ...
0
votes
2answers
367 views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
0
votes
1answer
187 views

Export/Import Subset of data in postgres

Using the following query, I'm able to export two tables, selecting 500 items randomly from the first: copy (select * from ( select 1 + floor(random() * 1040000)::integer as "documentUUID" ...
4
votes
1answer
251 views

What is the safe+fast way to move all Postgresql data to another machine?

I need to copy all data from one Postgresql 9.2.4 server to another (both on similar hardware, Ubuntu 12.04). Is it safe to shut down the source Postgres server, rsync the data directory to the new ...
-1
votes
1answer
70 views

Renaming a column in a PostgreSQL database table

This is essentially the same question as How do I rename a column in a database table using SQL?. So, I want to rename a column in a table. This column is part of a set of tables that lives inside a ...
1
vote
1answer
281 views

Client Encoding Mismatch when creating new database

I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run ...
0
votes
2answers
773 views

Backup with pgAdmin ordered by ID

I want to backup a postgres table with pgAdmin and have the dump ordered by ID (or any other column). How do I do that? [EDIT] The dump should contain INSERT queries, not the pure data! [/EDIT] What ...
0
votes
1answer
509 views

PostgreSql allocate memory for each connection

When we configure the memory setting for the Postgres DB what is the recommended memory allocation for each connection ? Is there any formula to apply ? I know 25% of the memory of the Server should ...
1
vote
1answer
2k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
0
votes
0answers
61 views

Postgresql function taking long time on newly restored database

I have developed one function in my PostgreSQL database. It was taking 12-15 seconds to execute. Now I have restored a new PostgreSQL 9.0 dump using following command: pg_restore -hx.x.x.x -p5432 -d ...
0
votes
1answer
507 views

Running a UPDATE on 3 million records

I have two actors in this transaction. One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table ...
2
votes
4answers
233 views

psql, getting a list of databases to be parsed by a script

I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them: psql -l -A -t but the output shows an obvious issue: the records are ...
1
vote
1answer
125 views

Benefit to keeping a record in the database, rather than deleting it, for performance issues?

So I have a client that I am building a Rails app for....I am using PostgreSQL. He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I ...
0
votes
1answer
75 views

Why my query works on VIEW and doesn't work on a similar table?

I have a view openstreetmapview that combines two tables with different columns. The first table is openstreetmap and the second is B. When I execute this query on the view: select this_.gid ...
-1
votes
1answer
215 views

Recovering a dropped column?

I have a PostgreSQL table: CREATE TABLE essays ( id serial primary key, content text ); I inserted a few rows, then ran: ALTER TABLE essays DROP COLUMN content; How can I recover the ...
1
vote
0answers
137 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
-1
votes
1answer
169 views

PostgreSQL indeterminate ordering of results [closed]

When I have this query: SELECT DISTINCT "spree_products".id FROM "spree_products" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND ...
-1
votes
2answers
854 views

pg_dump format => custom vs sql

There are 2 main formats for pg_dump: custom vs sql. For custom, it's compressed by default, and not readable if you try to open it. But is it faster to dump into this format as opposed to SQL format. ...
2
votes
2answers
2k views

How to dump PostgreSQL database whose owner has no password?

First, some background on my setup: The server has root access disabled. So, I log-in as (say) john who also belongs to the sudo group and is therefore able to run superuser commands. I created a ...
1
vote
1answer
104 views

Replicating from master to slaves and from slaves to master

I have a master and multiple slave machines. All SQL operations on the master node should be sent to the slave nodes. But the tables on the slave nodes may also be written to locally. These local ...
3
votes
1answer
1k views

Postgres Dump To TXT File

Is there a way to convert a Postgres dump file to a text file? I'm experiencing issues getting the dump to the proper file location of I would just restore from the dump file and then export to text. ...
0
votes
1answer
1k views

How to make Postgres autovacuum not impact performance?

I am running into problems where Postgres autovacuum processes are interfering with the overall performance of my database. It is making my select, insert, and update queries slower than usual. What ...
2
votes
1answer
1k views

Postgres nested hstore select query

I have a carts table with items hstore column. An example entry in this column is: carts.items row#1 = { "1614" => {:quantity=>"100", :price_cents=>1655}, ...
0
votes
1answer
200 views

Deciding the best way to partition (Postgresql)

I have been trying to research this for quite a while now and I am having a hard time trying to figure it out. I am out of ideas other then setting up an extra test database and running through all of ...
4
votes
2answers
652 views

Postgres DDL and DML best practices and coding standards

Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend. I am looking for something similar to what Google has for programming. ...
1
vote
1answer
181 views

What are the circumstances where autovacuum can be disabled?

I have a table where I am performing only inserts, never deletes/updates. I notice sometimes that autovacuum runs on this table, even though this is the case. autovacuum: VACUUM ANALYZE ...
2
votes
2answers
245 views

how to create an incremental database copy in postgresql?

Virtual machine software like VirtualBox allow one to make incremental VM clones. That is data, once "touched" (opened writable), will be copied and stored in the incremental cache of the new clone. ...
2
votes
2answers
371 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
0
votes
0answers
180 views

Streaming Replication Resource Management (PGSQL) on FreeBSD 8.x

I am attempting to set up PGSQL-HA streaming replication on freeBSD 8.2 (willing to upgrade to 8.4 if need be). The set up is simple: 2 nodes, one active one standby. I have successfully set up ...
-3
votes
3answers
2k views

How to get table definition of a PostgreSQL table? [closed]

I would like to get field's name, datatype of each field, length of each field and precision of each field of a PostgreSQL table. Please let me SQL query to get this information.
2
votes
2answers
2k views

What am I doing wrong with pg_hba.conf?

I'm trying to make it so that local connections to the database don't require a login. So I added the line local all all trust to the pg_hba.conf file, but then when I load pgAdmin and try to ...
1
vote
1answer
605 views

Disabling triggers in stored procedures

I am building a database application that uses functions and triggers. One trigger I have limits the possible data that can be put in on INSERT to make sure a record goes through a defined set of ...
1
vote
1answer
335 views

showing locks from all postgres relations

I'm trying to show all locks (DBMS-wide) in Postgres. I initially tried adapting the documented example to: SELECT pg_database.datname AS database, relation::regclass, pg_locks.database AS db_oid, ...
0
votes
2answers
300 views

Why isn't postgres prompting me about a new user?

I'm following these instructions to get postgres working with rails on Windows 7: Run "Start Command Prompt with Ruby" then... createuser -U postgres -P NewUserName When it prompts for a ...
2
votes
1answer
2k views

Can listen_addresses really be set to a list?

I have a VM with IP address 192.168.0.192 running postgreSQL. If I specify listen_addresses = '*' then I can connect from another VM at 192.168.0.191 and from localhost. But I can't seem to use a ...
2
votes
1answer
402 views

Configuring PostgreSQL to match server configuration

We are currently running web application and database server on singe dedicated machine. This is hardware configuration - http://www.hetzner.de/en/hosting/produkte_rootserver/ex10. Around 50Gb of ram ...
0
votes
1answer
163 views

How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?

I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like: add_index :users, [:username, :created_at] That should ...
1
vote
0answers
268 views

hbase vs postgres performance comparison? [closed]

I know that this is comparing apples to oranges, but sometimes you have to choose what to eat... I'm trying to find a rule of thumb as to the magnitudes for which to choose hbase over postgres. E.g., ...
0
votes
1answer
164 views

ERROR: text search configuration name “english” must be schema-qualified [closed]

Trying to do some full text searching with postgres and when running the following command UPDATE "EMAIL" SET PostText = to_tsvector('pg_catalog.english', "TEXT"); I'm getting the error: ERROR: ...
1
vote
1answer
255 views

Geohash implementation in PostgreSQL

Are there any geohash implementation for PostgreSQL? I couldn't find any.
3
votes
2answers
273 views

Possible to have nested inserts in Postgres 8.4?

I'm trying to import data into an inventory type database. Here's what I have for the tables (truncated for brevity): create type hostrole as enum ('Physical', 'Virtual', 'Hypervisor', 'Other'); ...
3
votes
2answers
375 views

Equivalents to `ora_rowscn` in other databases?

Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, ...
0
votes
1answer
93 views

What's stored in data_dir/base of postgresql

I suddenly found found my postgresql out of space. It's a tmp instance running on ram disk. Limited space (around 800M) allowed. Data are not actually stored on it, all table will be dropped after ...
1
vote
1answer
72 views

Is it a good way to use different schemas for storing the data and provide data access?

For a new product we came along with a pattern, where all the data tables are stored in one schema (lets call it storage), while the clients connect to another one (here called api). The api schema ...
0
votes
1answer
328 views

Getting locked out of PostgreSQL databases

One of the greatest fears I have is getting locked out of my own data due to the security features of a heavy weight database such as PostgreSQL (and PostGIS). Whilst I can log in as the ...
0
votes
1answer
169 views

Disaster Recovery for PostgreSQL 9.0

We have a number of PostgreSQL 9.0 servers. We use binary replication to have a host standby instance of those. The only problem is that is someone drops the master, with or without intentions, this ...
1
vote
1answer
171 views

Using a PostgreSQL tablespace from two different servers

I have a PostgreSQL database living on an external hard disk (RAID0 via Thunderbolt). So far I accessed it from a PostgreSQL server running on my MacBook Pro. As my work on this database is getting ...
2
votes
2answers
7k views

Connecting to an external database with pgAdmin III

I'm trying to connect to an external database from pgAdmin III (which is installed on both machines). The client complains: Access to database denied The server doesn't grant access to the ...
0
votes
1answer
33 views

Retrieve list of matched words in PostgreSQL

I'm new to PostgreSQL and really more than your more basic queries/inserts. I've created a TSVector column to my table that I'm searching and have set the column to look at the specific column in ...
1
vote
1answer
907 views

What to look for in bad pg_dump log

We want to programmatically detect errors in cron scheduled pg_dumps. Apart from cheking whether or not the log file ends in "pg_dump: saving database definition": What other tell-tale string can I ...