Search Results

Search found 624 results on 25 pages for 'postgres'.

Page 1/25 | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

• Postmaster uses excessive CPU and Disk Writes

  - by wolfcastle

  using PostgreSQL 9.1.2 I'm seeing excessive CPU usage and large amounts of writes to disk from postmaster tasks. This happens even while my application is doing almost nothing (10s of inserts per MINUTE). There are a reasonable number of connections open however. I've been trying to determine what in my application is causing this. I'm pretty newb with postgresql, and haven't gotten anywhere so far. I've turned on some logging options in my config file, and looked at connections in the pg_stat_activity table, but they are all idle. Yet each connection consumes ~ 50% CPU, and is writing ~15M/s to disk (reading nothing). I'm basically using the stock postgresql.conf with very little tweaks. I'd appreciate any advice or pointers on what I can do to track this down. Here is a sample of what top/iotop is showing me: Cpu(s): 18.9%us, 14.4%sy, 0.0%ni, 53.4%id, 11.8%wa, 0.0%hi, 1.5%si, 0.0%st Mem: 32865916k total, 7263720k used, 25602196k free, 575608k buffers Swap: 16777208k total, 0k used, 16777208k free, 4464212k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 17057 postgres 20 0 236m 33m 13m R 45.0 0.1 73:48.78 postmaster 17188 postgres 20 0 219m 15m 11m R 42.3 0.0 61:45.57 postmaster 17963 postgres 20 0 219m 16m 11m R 42.3 0.1 27:15.01 postmaster 17084 postgres 20 0 219m 15m 11m S 41.7 0.0 63:13.64 postmaster 17964 postgres 20 0 219m 17m 12m R 41.7 0.1 27:23.28 postmaster 18688 postgres 20 0 219m 15m 11m R 41.3 0.0 63:46.81 postmaster 17088 postgres 20 0 226m 24m 12m R 41.0 0.1 64:39.63 postmaster 24767 postgres 20 0 219m 17m 12m R 41.0 0.1 24:39.24 postmaster 18660 postgres 20 0 219m 14m 9.9m S 40.7 0.0 60:51.52 postmaster 18664 postgres 20 0 218m 15m 11m S 40.7 0.0 61:39.61 postmaster 17962 postgres 20 0 222m 19m 11m S 40.3 0.1 11:48.79 postmaster 18671 postgres 20 0 219m 14m 9m S 39.4 0.0 60:53.21 postmaster 26168 postgres 20 0 219m 15m 10m S 38.4 0.0 59:04.55 postmaster Total DISK READ: 0.00 B/s | Total DISK WRITE: 195.97 M/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 17962 be/4 postgres 0.00 B/s 14.83 M/s 0.00 % 0.25 % postgres: aggw aggw [local] idle 17084 be/4 postgres 0.00 B/s 15.53 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle 17963 be/4 postgres 0.00 B/s 15.00 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle 17188 be/4 postgres 0.00 B/s 14.80 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle 17964 be/4 postgres 0.00 B/s 15.50 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle 18664 be/4 postgres 0.00 B/s 15.13 M/s 0.00 % 0.23 % postgres: aggw aggw [local] idle 17088 be/4 postgres 0.00 B/s 14.71 M/s 0.00 % 0.13 % postgres: aggw aggw [local] idle 18688 be/4 postgres 0.00 B/s 14.72 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle 24767 be/4 postgres 0.00 B/s 14.93 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle 18671 be/4 postgres 0.00 B/s 16.14 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle 17057 be/4 postgres 0.00 B/s 13.58 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle 26168 be/4 postgres 0.00 B/s 15.50 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle 18660 be/4 postgres 0.00 B/s 15.85 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle

  Read the article

• How to set the SPN for Postgres SSPI

  - by chotchki

  I am trying to setup Postgres to support SSPI/Kerberos, however I think that I have not found out what the correct SPN that is needed to get it working. The background details: Service account for postgres: 'postgres' Domain Name: 'testdomain.com' Domain Controller: 'dc.testdomain.com' I have tried the following SPNs and have had zero luck: setspn -A HOST/testdomain.com postgres setspn -A HOST/testdomain postgres setspn -A POSTGRES/testdomain.com postgres setspn -A POSTGRES/testdomain postgres setspn -A POSTGRES/dc.testdomain.com postgres Does anyone have some suggestions?

  Read the article

• Pgagent startup script (under the postgres user)

  - by Dominique Guardiola

  Hello, I'm trying to make a clean startup script for pgagent I found one here but I don't see how I can change this : if start-stop-daemon --start --quiet --pidfile /var/run/pgagent.pid \ --exec /usr/bin/pgagent "hostaddr=127.0.0.1 dbname=postgres user=postgres \ password=XXXXXXX";then to launch something like this : su - postgres -c /usr/bin/pgagent "hostaddr=127.0.0.1 dbname=postgres user=postgres" in order to avoid to hard-code the PG password in the script. This is possible using the .pgpass file feature. It works when I'm logged under the postgres user. So my only problem left is how to launch this command under the postgres user tried to add --user=postgres in the call, like mentioned here but it does not work.

  Read the article

• Postgres backup

  - by Abbass

  Hello, I have a Bacula script that does an automatic backup of a Postgres Database. The script makes two backups using (pg_dump) of the data base : The schema only and the data only. /usr/bin/pg_dump --format=c -s $dbname --file=$DUMPDIR/$dbname.schema.dump /usr/bin/pg_dump --format=c -a $dbname --file=$DUMPDIR/$dbname.data.dump The problem is that I can't figure out how to restore it with pg_restore. Do I need to create the database and the users before then restore the schema and finally the data. I did the following : pg_restore --format=c -s -C -d template1 xxx.schema.dump pg_restore --format=c -a -d xxx xxx.data.dump This first restore creates the database with emtpy tables but the second gives many error like this one : pg_restore: [archiver (db)] COPY failed: ERROR: insert or update on table "Table1" violates foreign key constraint "fkf6977a478dd41734" DETAIL: Key (contentid)=(1474566) is not present in table "Table23". Any ideas?

  Read the article

• Connect to Postgres remotely, open port 5432 for Postgres in iptables

  - by Victor

  I am trying to connect to Postgres remotely but I need to open port 5432 in iptables. My current iptables configuration is as follows: *filter # Allows all loopback (lo0) traffic and drop all traffic to 127/8 that doesn't use lo0 -A INPUT -i lo -j ACCEPT -A INPUT ! -i lo -d 127.0.0.0/8 -j REJECT # Accepts all established inbound connections -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT # Allows all outbound traffic # You can modify this to only allow certain traffic -A OUTPUT -j ACCEPT # Allows HTTP and HTTPS connections from anywhere (the normal ports for websites) -A INPUT -p tcp --dport 80 -j ACCEPT -A INPUT -p tcp --dport 443 -j ACCEPT # Allows SSH connections # # THE -dport NUMBER IS THE SAME ONE YOU SET UP IN THE SSHD_CONFIG FILE # -A INPUT -p tcp -m state --state NEW --dport 30000 -j ACCEPT # Allow ping -A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT # log iptables denied calls -A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables denied: " --log-level 7 # Reject all other inbound - default deny unless explicitly allowed policy -A INPUT -j REJECT -A FORWARD -j REJECT COMMIT What would I have to add in iptables to open the port? I'm trying to install phppgadmin on a different server to access the postgres database. Thank you.

  Read the article

• How do you find the disk size of a Postgres / PostgreSQL table and its indexes

  - by mmrobins

  I'm coming to Postgres from Oracle and looking for a way to find the table and index size in terms of bytes/MB/GB/etc, or even better the size for all tables. In Oracle I had a nasty long query that looked at user_lobs and user_segments to give back an answer. I assume in Postgres there's something I can use in the information_schema tables, but I'm not seeing where. Thanks in advance.

  Read the article

• Postgres createuser.exe silent execution from batch script

  - by Woot4Moo

  I am finding myself with the issue of needing to execute the postgres createuser.exe from a batch script and cannot get it to stop prompting me with the following: Enter name of role to add: my batch script looks like this: echo calling createuser! createuser username %super_user% -s -U Super_Postgres s -q Where %super_user% is a command line argument. Any help would be greatly appreciated, this is the documentation that I am referring too: postgres

  Read the article

• How do you find the disk size of a Postgres table and its indexes

  - by mmrobins

  I'm coming to Postgres from Oracle and looking for a way to find the table and index size in terms of bytes/MB/GB/etc, or even better the size for all tables. In Oracle I had a nasty long query that looked at user_lobs and user_segments to give back an answer. I assume in Postgres there's something I can use in the information_schema tables, but I'm not seeing where. Thanks in advance.

  Read the article

• How do you find the row count for all your tables in Postgres

  - by mmrobins

  I'm looking for a way to find the row count for all my tables in Postgres. I know I can do this one table at a time with a select count(*) from table_name; but I'd like to see the row count for all the tables and the order by that to get an idea of how big all my tables are.

  Read the article

• How to restart postgres on OS X?

  - by Scott Semple

  After a reboot, postgres on my machine hasn't restarted. What command should I use in Terminal to get it going again?

  Read the article

• Cannot login to Postgrest database despite setting password for user 'postgres'

  - by Serg

  I'm trying to use pgAdmin III to manage my Postgres database. Here are the commands I've run on my machine: sudo apt-get install postgresql Then I installed the pgAdmin III application: sudo apt-get install pgadmin3 Next I focused on setting my username and password in order to login: sudo -u postgres psql postgres Here I set my password \password postgres Finally I just created my database: sudo -u postgres createdb repairsdatabase When I try to login using pgAdmin III, I get the error: An error has occurred: Error connecting to the server: FATAL: Peer authentication failed for user "postgres"

  Read the article

• Cannot connect to postgres installed on Ubuntu

  - by Assaf

  I installed the Bitnami Django stack which included PostgreSQL 8.4. When I run psql -U postgres I get the following error: psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? PG is definitely running and the pg_hba.conf file looks like this: # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 What gives? "Proof" that pg is running: root@assaf-desktop:/home/assaf# ps axf | grep postgres 14338 ? S 0:00 /opt/djangostack-1.3-0/postgresql/bin/postgres -D /opt/djangostack-1.3-0/postgresql/data -p 5432 14347 ? Ss 0:00 \_ postgres: writer process 14348 ? Ss 0:00 \_ postgres: wal writer process 14349 ? Ss 0:00 \_ postgres: autovacuum launcher process 14350 ? Ss 0:00 \_ postgres: stats collector process 15139 pts/1 S+ 0:00 \_ grep --color=auto postgres root@assaf-desktop:/home/assaf# netstat -nltp | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 14338/postgres tcp6 0 0 ::1:5432 :::* LISTEN 14338/postgres root@assaf-desktop:/home/assaf#

  Read the article

• postgres - ERROR: syntax error at or near "COST"

  - by cino21122

  EDIT Taking COST 100 out made the command go through, however, I'm still unable to run my query because it yields this error: ERROR: function group_concat(character) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. The query I'm running is this: select tpid, group_concat(z) as z, group_concat(cast(r as char(2))) as r, group_concat(to_char(datecreated,'DD-Mon-YYYY HH12:MI am')) as datecreated, group_concat(to_char(datemodified,'DD-Mon-YYYY HH12:MI am')) as datemodified from tpids group by tpid order by tpid, zip This function seems to work fine locally, but moving it online yields this error... Is there something I'm missing? CREATE OR REPLACE FUNCTION group_concat(text, text) RETURNS text AS $BODY$ SELECT CASE WHEN $2 IS NULL THEN $1 WHEN $1 IS NULL THEN $2 ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2 END $BODY$ LANGUAGE 'sql' IMMUTABLE COST 100; ALTER FUNCTION group_concat(text, text) OWNER TO j76dd3;

  Read the article

• Good Postgres graphical client for Windows

  - by alex

  The name pretty much says it all. Right now I'm using Squirrel - it crashes frequently and suffers from memory problems (I've tried increasing the heap size). I don't need anything particularly fancy or full-featured - just something that won't take up 2.4 GB of RAM to store a 1.5 million line, 8 column result set.

  Read the article

• postgres - ERROR: operator does not exist

  - by cino21122

  Again, I have a function that works fine locally, but moving it online yields a big fat error... Taking a cue from a response in which someone had pointed out the number of arguments I was passing wasn't accurate, I double-checked in this situation to be certain that I am passing 5 arguments to the function itself... Query failed: ERROR: operator does not exist: point <@> point HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. The query is this: BEGIN; SELECT zip_proximity_sum('zc', (SELECT g.lat FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1), (SELECT g.lon FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1), (SELECT m.zip FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1) ,10); The PG function is this: CREATE OR REPLACE FUNCTION zip_proximity_sum(refcursor, numeric, numeric, character, numeric) RETURNS refcursor AS $BODY$ BEGIN OPEN $1 FOR SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance FROM geocoded g LEFT JOIN masterfile r ON g.recordid = r.id WHERE (geo_distance( point($2,$3),point(g.lat,g.lon)) < $5) ORDER BY r.zip, distance; RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;

  Read the article

• Postgres 8.4 stackbuilder not run in Ubuntu12.04

  - by vikas

  I have installed Postgres 8.4 in my Ubuntu 12.04 , but after installation it does not run stackbuilder.It also not running from menu bar . How can i run stackbuilder ? edit:I install this from postgres-8.4.run package.It uses a GUI interface.After installing postgres it should run stackbuilder but it not run stackbuilder GUI. This prob is related to ubuntu 12.04 because in 11.04, I have used this application.This question also asked on other forms but there is no solution. Thank you.

  Read the article

• Administer postgres from PGAdmin on remote mac using ssh tunnel

  - by Aidan Ewen

  I've got PostgreSQL installed on a Ubuntu server and I'm trying to connect to that server using PGAdmin on a remote macbook. I've created an ssh tunnel - macbook:~postgres$ ssh -L 5423:localhost:5432 [email protected] And I can connect using psql on the macbook as expected - macbook:~ me$ psql -U postgres -p 5423 -h localhost ... postgres=# In the 'New Server Registration' window on PGAdminIII I'm entering the following credentials - Name - MyServer Host - localhost Port - 5423 Maintenance DB - postgres Username - postgres Password - <remote_postgres_password> However the connection fails - Error connecting to the server: FATAL: password authentication failed for user "postgres" Not sure what's going on here, these seem to be the same credentials I've used for psql.

  Read the article

• Why doens't my Postgres user have permissions to add a Postgres database?

  - by orokusaki

  First, I ran: sudo su postgres createuser -U postgres foouser -P which worked fine, and I ran: createdb -U foouser -E utf8 -O foouser foodatabase -T template0 and got "permission denied: cannot create database" Firstly, should I even su as postgres to do operations like the first one (assuming my postgres data dir is owned by postgres), or is -U postgres from any user (assuming trust is used in pg_hba.conf) sufficient? Secondly, why am I running into this error? Is this because the user foouser is a non-superuser? Should I create foodatabase using the postgres user and simply -O foouser?

  Read the article

• Locate a user's bashrc file

  - by Starkers

  Really confused. Upon running cat etc/passwd I have found this: postgres:x:117:126:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash meaning I have a postgres user, right? I want to change the bashrc environment file of this user to make commands available to it. /var/lib/postgresql doesn't contain a bashrc file, and /bin/bash doesn't contain it either, so I don't really know what's going on. All I know is a created postgres using the useradd command, so why do I have some weird user with no home directory? So confused :(

  Read the article

• "No version information available" - After installing Postgres

  - by intellidiot

  I have installed Postgres 9.1.4 on an Ubuntu 12.04 (precise) 64-bit from here http://www.openscg.com/se/postgresql/packages.jsp, but right after installing many commands (programs) are throwing these following warnings in different combinations: /opt/postgres/9.1/lib/libxml2.so.2: no version information available /opt/postgres/9.1/lib/libcrypto.so.1.0.0: no version information available /opt/postgres/9.1/lib/libssl.so.1.0.0: no version information available Though this is not restricting anything, this is often getting very annoying. Is there a way to get rid of this without uninstalling Postgres?

  Read the article

• PostGres Error When Using Distinct : postgres ERROR: could not identify an ordering operator for ty

  - by CaffeineIV

  ** EDIT ** Nevermind, just needed to take out the parens... I get this error: ERROR: could not identify an ordering operator for type record when trying to use DISTINCT Here's the query: select DISTINCT(g.fielda, g.fieldb, r.type) from fields g LEFT JOIN types r ON g.id = r.id; And the errors: ERROR: could not identify an ordering operator for type record HINT: Use an explicit ordering operator or modify the query. ********** Error ********** ERROR: could not identify an ordering operator for type record SQL state: 42883 Hint: Use an explicit ordering operator or modify the query.

  Read the article

• What schema documentation tools exist for PostgreSQL

  - by Brad Koch

  MySQL has MySQL Workbench for designing and documenting your schema, and generates CREATE and ALTER scripts based on your design. We're looking at migrating to PostgreSQL in the near future, and we do need a practical way of documenting and modifying the schema structure. What similar tools exist for Postgres (that are OS X/Linux compatible)? Alternatively, what equivalent conventions would be followed for designing and documenting the structure of your Postgres database?

  Read the article

• How to reset the postgres super user password on mac os x

  - by Andrew Barinov

  I installed postgres on my mac running 10.6.8 and I would like to reset the password for the postgres user (I believe this is the super user password) and then restart it. All the directions I found do not work because I think my user name is not recognized by pg as having authority to change the password. (I am on the admin account of my mac) Here is what I tried: Larson-2:~ larson$ psql -U postgres Password for user postgres: psql (9.0.4, server 9.1.2) WARNING: psql version 9.0, server version 9.1. Some psql features might not work. Type "help" for help. postgres=# ALTER USER postgres with password 'mypassword' postgres-# \q and for restart I did: Larson-2:~ larson$ su postgres -c 'pg_ctl -D /opt/local/var/db/postgresql84/defaultdb/ restart > Which didn't work, as the password remained the same as it was before. Can someone provide directions for doing this and for making sure it's recognized by PG? Update I went ahead and edited the pg_hba.conf file located in /Library/PostgreSQL/9.1/data and set the settings as follows: # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust However, like before, the password stayed the same after I changed it. I am not sure what further steps I can take from here.

  Read the article

• Cannot start Postgres daemon after installing with Yum

  - by Sean the Bean

  I was trying to install Postgres 9.1.4 on Fedora 17 using Yum. If I do: sudo yum install postgres-libs sudo yum install postgres sudo yum install postgis All the installs appear to complete successfully (i.e., no errors), but I cannot start the Postgres daemon using: service postgresql initdb Like the official Postgres download guide says to do (http://www.postgresql.org/download/linux/redhat/). The error says Unknown operation initdb. RPM tells me that it installed psql to /usr/bin/, which I confirmed. It turns out that only a few components installed correctly (psql, pg_dump, pg_configure, and a few others), but most are missing (e.g., pg_ctl and postgres). I've tried several different configurations and had several of my coworkers (with more linux experience than me) look at it, but so far nothing has worked. Two of them have also run into similar issues installing Postgres using apt-get on Ubuntu, which makes me think the rpm isn't doing its job. It seems the only solution to build it from source, which is more robust anyway, but of course it takes longer. I'm wondering, though, if anyone else has run into this issue and/or has successfully installed Postgres on either Fedora or Ubuntu using a package manager like yum or apt-get? Is the rpm broken?

  Read the article

• How do I install Postgres Graphical Installer?

  - by Tusar Das

  I am a newbie in using Ubuntu (more precisely never used linux except the production deployment ). Now I choose to hold a grip on the system and intalled a latest 11.10 Ubuntu in my 32 bit Intel** desktop. In this machine **I have a 'administrator' user 'tusar'. I want to install the PostgreSQL graphical version so downloaded this file postgresql-8.4.9-1-linux.bin from here Now If run the file with ./filename command (refuses if I am not root), it opens up the graphical interface and completes the installation (creates user postgres and ask for password, wont complete if I dont give valid password). I checked with /etc/inint.d/postgresql-8.4 status , that postgres is running. But the problem is I am unable to open the GUI (I installed it in /opt/Postgresql ), its telling permission denied even if I am logged in as 'tusar'. Then I tried using the su postgres command and it directly changed the user without password. Now if I run the psql , createdb , its prompting me to install the postgres-client-package but this utilies are already installed and I can view them in my /opt/Postgresql/8.4/bin folder. I can run query through my JDBC utility programs but unable to use neither terminal nor the graphical interface (although I can see pgAdmin3 is there ). Urgent help needed. I have used this guide for the installation.

  Read the article

1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >