Re: [GENERAL] Export binary data - PostgreSQL 9.2
Hi, You can use pg_dump with -t and -Fc option to take dump of a table in compressed format. $PGBIN/pg_dump -t -Fc -d -f /tmp/table.dmp For more information you can refer below link: http://www.postgresql.org/docs/9.2/static/app-pgdump.html Hope this would help. On Thu, Mar 3, 2016 at 1:21 AM, drum.lu...@gmail.com wrote: > > > On 29 February 2016 at 06:31, Steve Crawford < > scrawf...@pinpointresearch.com> wrote: > >> What exactly are you trying to do? Dump/backup your data (e.g. pg_dump)? >> Read binary data from a table? If so, what field type (bytea, blob, ...)? >> Export to where? >> >> Cheers, >> Steve >> >> >> On Sun, Feb 28, 2016 at 9:12 AM, drum.lu...@gmail.com < >> drum.lu...@gmail.com> wrote: >> >>> Hi all, >>> >>> >>> Which command would be to export the binary data for a table? >>> >>> I was unable to find it... >>> >>> Thanks >>> >> >> > > Have just used the COPY WITH Binary > > Thank you. > -- Thanks & Regards, Abdul Sayeed PostgreSQL DBA Postgres Professional Certified EnterpriseDB Corp Skype: abdul.sayeed24
Re: [GENERAL] PostgreSQL DB Replication
Hi Rajesh, I don't know what error you are getting. Though you can use below workaround to login and change the password. Step 1: Open pg_hba.conf file and modify md5 to trust Step 2: reload the cluster (/opt/PostgreSQL/9.2/bin/pg_ctl -D ../data/ reload) Step 3: Connect to the database, it should not ask for password: bash-4.1$ ./psql -p 5435 postgres psql.bin (9.2.7) Type "help" for help. postgres=# \password postgres; Enter new password: Enter it again: postgres=# \q bash-4.1$ ./psql -p 5435 postgres Password: psql.bin (9.2.7) Type "help" for help. postgres=# If the above workaround does not work, please share what error you are getting. Hope this helps…. Thanks & Regards, Abdul Sayeed PostgreSQL DBA Postgres Professional Certified EnterpriseDB Corp Skype: abdul.sayeed24 On Tue, Aug 26, 2014 at 11:29 AM, K P Manoj wrote: > Hi Rajesh, > > Parameter hot_standby is on on slave server ?. You are trying to change > postgres password on master or slave ?. > > Regards > Manoj K P > 8050972028 > > > On Tue, Aug 26, 2014 at 10:15 AM, sivananda reddy > wrote: > >> Hi Rajesh >> >> Please share the below information: >> >> 1)postgresql.conf file (Both master and slave) >> 2) database server logs >> 3)recovery.conf file >> 4)pg_hba.conf file >> >> With Regards, >> Sivananda Reddy >> 07385114008 >> >> >> On Tue, Aug 26, 2014 at 9:34 AM, wd wrote: >> >>> What's the output when you try to login? Have your slave configured as a >>> standby server ? >>> >>> >>> On Mon, Aug 25, 2014 at 12:46 PM, Rajesh K wrote: >>> >>>> Dear Sir, >>>> >>>> I have planned to configure PostgreSQL 9.2.4 database Master and Slave >>>> replication on Redhat Linux Server 6.As per the documentation i have done >>>> the settings on both Master and Slave.I choose 7 simple step binary >>>> replication method as per the PostgreSQL website.After DB Synchronization >>>> command I start the slave and master as per the documentation.After that I >>>> am not able to login as postgres user on slave and not able to change the >>>> password of the user postgres.Kindly help me to done the same . >>>> >>>> Thanks & Regards >>>> Rajesh.K/Pramod >>>> Developer >>>> Cochin >>>> 8129823272 >>>> >>>> >>>> >>>> >>>> >>> >> >
Re: [GENERAL] Postgresql not getting assigned memory
Hi, It shows 2GB. It means it is updated. Shared_buffer memory is used at postgresql database level, It will consume while doing operation such as INSERT, UPDATE, DELETE and other maintenance task (VACUUM/VACUUM FULL). Thanks, Abdul On Fri, Jun 20, 2014 at 6:28 PM, Oliver wrote: > Thank you very much to all. > > postgres=# show shared_buffers; > shared_buffers > > 2GB > (1 row) > > Is it ok then, isn't it? DB hasn't activity now because it is a new > instance, top command indicates only 350MB of used memory .. I understand > that postgresql could reach until 2GB of server memory as total, correct? > Thanks beforehand. > > Cheers... > > > > > 2014-06-20 13:54 GMT+01:00 Abdul Sayeed : > > Hi, >> >> Can you please check the output of below command: >> >> postgres=# show shared_buffers ; >> >> >> >> Thanks & Regards, >> Abdul Sayeed >> System Engineer >> >> >> The Postgres Database Company >> >> Are you updated: Latest version of Postgres Plus Advanced Server are >> 8.4.19.42, 9.0.16.34, 9.1.12.20, 9.2.8.19 and 9.3.4.10 >> >> To reach Support Call: >> US: +1-732-331-1320 - UK: +44 - 2033719820 >> Brazil: +55-2139581371 - India: +91-20-32676535 >> >> >> >> Website: www.enterprisedb.com >> EnterpriseDB Blog : http://blogs.enterprisedb.com >> Follow us on Twitter : http://www.twitter.com/enterprisedb >> >> CONFIDENTIALITY NOTICE >> This e-mail transmission and any documents, files, or previous e-mail >> messages appended or attached to it, may contain information that is >> confidential or legally privileged. If you are not the intended recipient, >> or a person responsible for delivering it to the intended recipient, you >> are hereby notified that you must not read this transmission and that any >> disclosure, copying, printing, distribution, or use of the information >> contained or attached to this transmission is STRICTLY PROHIBITED. If you >> have received this transmission in error, please immediately notify the >> sender by telephone or return e-mail message and delete the original >> transmission, its attachments, and any copies without reading or saving in >> any manner. Thank you. >> >> >> On Fri, Jun 20, 2014 at 5:21 PM, Oliver wrote: >> >>> Hello, >>> I'm new in postgresql, I'm sorry if I do something bad. >>> Default value or shared_buffers is 128MB, I have a dedicated server for >>> postgresql with 8GB RAM. I've changed shared_buffers value to 2048MB and >>> uncommented the entry in postgresql.conf. It shows now: >>> >>> # - Memory - >>> >>> shared_buffers = 2048MB # min 128kB >>> >>> When I restart instance or even server, pgstartup.log indicates: >>> >>> seleccionando el valor para shared_buffers ... 128MB (I'm sorry, I'm >>> spanish and server is configured in spanish). But it shows something as: >>> selecting shared_buffers value ... 128MB. >>> What I do bad? Thanks beforehand. >>> >>> Cheers... >>> >> >> >
Re: [GENERAL] Postgresql not getting assigned memory
Hi, Can you please check the output of below command: postgres=# show shared_buffers ; Thanks & Regards, Abdul Sayeed System Engineer The Postgres Database Company Are you updated: Latest version of Postgres Plus Advanced Server are 8.4.19.42, 9.0.16.34, 9.1.12.20, 9.2.8.19 and 9.3.4.10 To reach Support Call: US: +1-732-331-1320 - UK: +44 - 2033719820 Brazil: +55-2139581371 - India: +91-20-32676535 Website: www.enterprisedb.com EnterpriseDB Blog : http://blogs.enterprisedb.com Follow us on Twitter : http://www.twitter.com/enterprisedb CONFIDENTIALITY NOTICE This e-mail transmission and any documents, files, or previous e-mail messages appended or attached to it, may contain information that is confidential or legally privileged. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that you must not read this transmission and that any disclosure, copying, printing, distribution, or use of the information contained or attached to this transmission is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify the sender by telephone or return e-mail message and delete the original transmission, its attachments, and any copies without reading or saving in any manner. Thank you. On Fri, Jun 20, 2014 at 5:21 PM, Oliver wrote: > Hello, > I'm new in postgresql, I'm sorry if I do something bad. > Default value or shared_buffers is 128MB, I have a dedicated server for > postgresql with 8GB RAM. I've changed shared_buffers value to 2048MB and > uncommented the entry in postgresql.conf. It shows now: > > # - Memory - > > shared_buffers = 2048MB # min 128kB > > When I restart instance or even server, pgstartup.log indicates: > > seleccionando el valor para shared_buffers ... 128MB (I'm sorry, I'm > spanish and server is configured in spanish). But it shows something as: > selecting shared_buffers value ... 128MB. > What I do bad? Thanks beforehand. > > Cheers... >
Re: [GENERAL] Exact Patch Version
Hi Bruce, Thank you for the information. Best Regards, Abdul Sayeed DBA - EnterpriseDB Skype: abdul.sayeed24 On Sat, Jan 25, 2014 at 9:44 AM, Bruce Momjian wrote: > On Sat, Jan 25, 2014 at 05:02:23AM +0530, Abdul Sayeed wrote: > > Hi All, > > > > Could any one please confirm me in which community version "Fix multiple > bugs > > in index page locking during hot-standby WAL replay" has been fixed. > > Well, that patch shows as being applied to 9.3.X on: > > commit ebde6c40148c9f811f7c6d35f67e7ea3ce2d9b34 > Author: Tom Lane > Date: Tue Jan 14 17:34:51 2014 -0500 > > so it would be in the next minor release after that, but we have not had > any minor releases since then, so it is not released yet. > > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + Everyone has their own god. + >
[GENERAL] Exact Patch Version
Hi All, Could any one please confirm me in which community version "Fix multiple bugs in index page locking during hot-standby WAL replay" has been fixed. Your response in appreciated. Warm Regards, Abdul Sayeed DBA - EnterpriseDB Skype: abdul.sayeed24
Re: [GENERAL] How to list and describe tables in psql???
Hi All, Below are the details: *Connect to default database:* *=* bash-4.1$ ./psql -p 5432 postgres Password: psql.bin (9.1.7) Type "help" for help. postgres=# *To list all available database:* *=* postgres=# \l+ List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges | Size | Tablespace |Description ---+--+--+-+-+---+-++ pem | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+| 29 MB | pg_default | | | | | | pem_user=Tc/postgres +| || | | | | | pem_agent=Tc/postgres | || postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 6570 kB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 6457 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | || template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 6457 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | || (4 rows) *To change Database:* *===* postgres=# \c edb You are now connected to database "edb" as user "postgres". edb=# *To check list of schemas:* *===* postgres=# \dn List of schemas Name | Owner +-- abdul | postgres public | postgres (2 rows) *To set a Schema for a session:* *===* postgres=# set search_path to abdul; SET postgres=# show search_path ; search_path - abdul (1 row) postgres=# create table test (n numeric, Name varchar(10)); CREATE TABLE postgres=# insert into test values (generate_series(1,10),'TEST'); INSERT 0 10 postgres=# select * from test; n | name +-- 1 | TEST 2 | TEST 3 | TEST 4 | TEST 5 | TEST 6 | TEST 7 | TEST 8 | TEST 9 | TEST 10 | TEST (10 rows) *To list all tables of a current schema:* *===* postgres=# \dt+ List of relations Schema | Name | Type | Owner | Size | Description +--+---+--+---+- abdul | test | table | postgres | 16 kB | (1 row) Hope this helps. Thanks & Regards, Abdul Sayeed System Engineer The Postgres Database Company Are you updated: Latest version of Postgres Plus Advanced Server are 8.4.19.42, 9.0.15.33, 9.1.11.19, 9.2.6.16, 9.3.2.5. To reach Support Call: US: +1-732-331-1320 - UK: +44 - 2033719820 Brazil: +55-2139581371 - India: +91-20-32676535 Website: www.enterprisedb.com EnterpriseDB Blog : http://blogs.enterprisedb.com Follow us on Twitter : http://www.twitter.com/enterprisedb CONFIDENTIALITY NOTICE This e-mail transmission and any documents, files, or previous e-mail messages appended or attached to it, may contain information that is confidential or legally privileged. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that you must not read this transmission and that any disclosure, copying, printing, distribution, or use of the information contained or attached to this transmission is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify the sender by telephone or return e-mail message and delete the original transmission, its attachments, and any copies without reading or saving in any manner. Thank you. On Thu, Jan 2, 2014 at 4:34 PM, Raymond O'Donnell wrote: > On 02/01/2014 01:53, peterlen wrote: > > I am using PostgreSQL 9.1 and have boon looking for psql commands to > list all > > tables in the current database as well as how to "describe" a given > table. > > I have come across some info on the web (older mostly) that lists > commands > > like \d, \dt, or \d+ but I get results like "no relations > found" > > or "column reltriggers does not exist". Not sure what is going on. > > > > Can someone enlighten me on the right commands to use? > > Possibly a silly question, but are you connected to the correct > database? If you don't specify a database on the psql command line, then > psql connects to a database with the same name as the username you use > to connect, if such a database exists. > > Ra
Re: [GENERAL] How to find out unused indexes?
Hi, Below query can be handy to find unused indexes. select indexrelname from pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and idx_tup_fetch = 0 ; Hope it helps. Thanks & Regards, Abdul Sayeed EnterpriseDB Corporation - RDBA Team The Postgres Database Company Office: +1-732-931-7033/+91-20-305-89510 Fax: +91-20-30589502 Skype: abdul.sayeed24 Website: www.enterprisedb.com EnterpriseDB Blog : http://blogs.enterprisedb.com Follow us on Twitter : http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution,retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. On Tue, Sep 3, 2013 at 12:14 PM, Raghavendra < raghavendra@enterprisedb.com> wrote: > On Tue, Sep 3, 2013 at 11:36 AM, ascot.m...@gmail.com < > ascot.m...@gmail.com> wrote: > >> Hi, >> >> Can you please advise how to find out all unused indexes in PG? >> >> regards >> > > Below wiki link should help; under "Unused Index" section: > > http://wiki.postgresql.org/wiki/Index_Maintenance > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ > > >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
