Re: Reg: Query for DB growth size
Hi Andreas, I hope you did not understand my question. I am not asking query for current database size. I am asking for database growth, i mean predicting the increase in database size over a period like a month just like its available in oracle. For oracle you can see the below link. http://www.dba-oracle.com/t_database_growth_reports.htm Thanks in advance. Regards, PraveenRaja From: Andreas Joseph Krogh Sent: Thursday, November 29, 2018 1:10 PM To: pgsql-general@lists.postgresql.org Subject: Sv: Reg: Query for DB growth size På torsdag 29. november 2018 kl. 07:41:24, skrev R.R. PRAVEEN RAJA mailto:rrpraveenr...@live.com>>: Hi All, Can i get the query or steps for how to check the Database growth in postgres. Thanks in advance. select pg_size_pretty(pg_database_size(current_database())); -- Andreas Joseph Krogh
Re: Reg: Query for DB growth size
Hello, On 29/11/18 10:15 π.μ., R.R. PRAVEEN RAJA wrote: Hi Andreas, I hope you did not understand my question. I am not asking query for current database size. I am asking for database growth, i mean predicting the increase in database size over a period like a month just like its available in oracle. For oracle you can see the below link. http://www.dba-oracle.com/t_database_growth_reports.htm A huge list of monitoring solutions for Unix/Linux/PostgreSQL. The hot new trend is Prometheus, also you could checkout nagios, icinga, munin, zabbix lots and lots of options there. You might also try a commercial offering (like e.g. Several Nines), they have some pretty graphs, alarms, alerts of all kinds. And last but not least, you could have your own monitoring table where you keep periodic snapshots of the live metrics. You may read here for an overview : https://severalnines.com/blog/key-things-monitor-postgresql-analyzing-your-workload Thanks in advance. Regards, PraveenRaja *From:* Andreas Joseph Krogh *Sent:* Thursday, November 29, 2018 1:10 PM *To:* pgsql-general@lists.postgresql.org *Subject:* Sv: Reg: Query for DB growth size På torsdag 29. november 2018 kl. 07:41:24, skrev R.R. PRAVEEN RAJA mailto:rrpraveenr...@live.com>>: Hi All, Can i get the query or steps for how to check the Database growth in postgres. Thanks in advance. select pg_size_pretty(pg_database_size(current_database())); -- *Andreas Joseph Krogh* -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: Reg: Query for DB growth size
On Thu, Nov 29, 2018 at 08:15:10AM +, R.R. PRAVEEN RAJA wrote: > I hope you did not understand my question. I am not asking query for > current database size. I am asking for database growth, i mean > predicting the increase in database size over a period like a month > just like its available in oracle. Why not running a cron job which stores periodically the result of pg_database_size with a timestamp (simply now())? Then you have as much data as you want for growth comparison. -- Michael signature.asc Description: PGP signature
Re: Reg: Query for DB growth size
On 29-11-2018 08:41, R.R. PRAVEEN RAJA wrote: Hi All, Can i get the query or steps for how to check the Database growth in postgres. Thanks in advance. Regards, Praveen No official thing that can do the job. You need to DIY, start collecting data (database size, connections count, index sizes or so) every day, hour, min or so and then make software to analyze it after a while. Regards, HS
Re: Triggers when importing data
On 11/28/18 9:43 PM, Sathish Kumar wrote: Hi, I am trying to export and import sql file of a database. I would like to know whether it will execute all the triggers when importing the sql dump which is for Insert or Update or Delete. Well you command as constructed below will have no INSERTs(or UPDATES OR DELETES). The data will be moved into the tables using COPY. Even if you use --inserts(https://www.postgresql.org/docs/10/app-pgdump.html) that is all you will get. Can you explain more what it is you are trying to achieve or expect? Export:pg_dump -h test -U db_admin --format=plain --no-owner --no-acl production | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > test.sql Import: psql -h test -U db_admin -d production -W < test.sql -- Adrian Klaver adrian.kla...@aklaver.com
Dump table using pg_dump vs pg_restore -f
Postgres 10.6 I am getting different output using: pg_dump -d production -U postgres -s -t container -f cont.sql vs pg_restore -s -t container -f container.sql production_112818.out For the pg_dump version I get: CREATE TABLE public.container ( c_id character varying(10) NOT NULL, cdesc character varying(30) NOT NULL, cell_per smallint NOT NULL, c_size character varying(10) NOT NULL, ts_insert timestamp(0) without time zone DEFAULT now(), ts_update timestamp(0) without time zone, user_update character varying(20), user_insert character varying(20) DEFAULT "session_user"(), v_number integer, us_vol numeric(5,2), metric_vol numeric(5,2), metric_unit character varying(3), c_number character varying(20), us_unit character varying(5), c_note text, c_units integer NOT NULL ); ALTER TABLE public.container OWNER TO postgres; -- -- Name: TABLE container; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON TABLE public.container IS 'Base table for plant container info. Used as source of c_id for other tables.'; -- -- Name: container container_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.container ADD CONSTRAINT container_pkey PRIMARY KEY (c_id); -- -- Name: container container_delete; Type: TRIGGER; Schema: public; Owner: postgres -- CREATE TRIGGER container_delete AFTER DELETE ON public.container FOR EACH ROW EXECUTE PROCEDURE utility.archive_del_record(); ... more triggers For the pg_restore I get: CREATE TABLE public.container ( c_id character varying(10) NOT NULL, cdesc character varying(30) NOT NULL, cell_per smallint NOT NULL, c_size character varying(10) NOT NULL, ts_insert timestamp(0) without time zone DEFAULT now(), ts_update timestamp(0) without time zone, user_update character varying(20), user_insert character varying(20) DEFAULT "session_user"(), v_number integer, us_vol numeric(5,2), metric_vol numeric(5,2), metric_unit character varying(3), c_number character varying(20), us_unit character varying(5), c_note text, c_units integer NOT NULL ); ALTER TABLE public.container OWNER TO postgres; -- -- PostgreSQL database dump complete -- In the docs: https://www.postgresql.org/docs/10/app-pgrestore.html I see: " Note This flag does not behave identically to the -t flag of pg_dump. There is not currently any provision for wild-card matching in pg_restore, nor can you include a schema name within its -t. " That to me reads that the difference is for the search method for table names only. The description for: -t table Includes: "Restore definition and/or data of only the named table. ..." That in my mind would include the PK and the table triggers. Am I misunderstanding? -- Adrian Klaver adrian.kla...@aklaver.com
Debian : No echo after pg_dump | psql
Hi guys, I'm facing a strange thing on my test server (Google Cloud) On my Debian 9 box I'm running Postgres 9.6.10, and I'm transferring some databases from another server (Debian 8, PG 9.5.15). The command I'm using is root@x:~# pg_dump -v -C -h -p 6543 -U postgres | psql -h localhost -p 6543 -U postgres It presents a double password prompt after I run it: Password: Password for user postgres: I enter _once_ my postgres password, and it starts processing data. At a certain point it suddenly stops processing without prompting anything, and goes on only if I insert my postgres password again, as if it was for one of the two prompts it showed at the beginning where I entered only one password. After another bit of processing, it asks another time (the third!) my postgres password. After I entered it, it goes to the end (and there are no issues in data or anything simia. After this, I have no more echo on what I'm typing, but if I type something and press enter, I can see the result of the command I just typed (and have not seen) on the console. Restarting the SSH session resolves the problem. The same strange behavior is the same if I switch servers (running pg_dump on Debian 8/PG 9.5 connecting on Debian 9/PG 9.6) Another thing I was forgetting to report: If I create database (just database, not schema) on target machine, I receive the error "database already exists" but the dump goes on If I don't create it, I receive the error "database does not exist" and processing aborts. Hope I've been clear enough.. Has someone bumped into it? Thanks in advance Moreno.-
Re: Debian : No echo after pg_dump | psql
On 11/29/18 8:12 AM, Moreno Andreo wrote: Hi guys, I'm facing a strange thing on my test server (Google Cloud) On my Debian 9 box I'm running Postgres 9.6.10, and I'm transferring some databases from another server (Debian 8, PG 9.5.15). The command I'm using is root@x:~# pg_dump -v -C -h -p 6543 -U postgres | psql -h localhost -p 6543 -U postgres It presents a double password prompt after I run it: Password: Password for user postgres: I enter _once_ my postgres password, and it starts processing data. At a certain point it suddenly stops processing without prompting anything, and goes on only if I insert my postgres password again, as if it was for one of the two prompts it showed at the beginning where I entered only one password. After another bit of processing, it asks another time (the third!) my postgres password. After I entered it, it goes to the end (and there are no issues in data or anything simia. After this, I have no more echo on what I'm typing, but if I type something and press enter, I can see the result of the command I just typed (and have not seen) on the console. Restarting the SSH session resolves the problem. The same strange behavior is the same if I switch servers (running pg_dump on Debian 8/PG 9.5 connecting on Debian 9/PG 9.6) Look into the .pgpass file: https://www.postgresql.org/docs/10/libpq-pgpass.html Or if you already have one check that is set up for both the remote and local servers. Another thing I was forgetting to report: If I create database (just database, not schema) on target machine, I receive the error "database already exists" but the dump goes on Might look at --clean: https://www.postgresql.org/docs/10/app-pgdump.html -c --clean Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.) This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore. If I don't create it, I receive the error "database does not exist" and processing aborts. Hope I've been clear enough.. Has someone bumped into it? Thanks in advance Moreno.- -- Adrian Klaver adrian.kla...@aklaver.com
Re: Debian : No echo after pg_dump | psql
> "Moreno" == Moreno Andreo writes: Moreno> The command I'm using is Moreno> root@x:~# pg_dump -v -C -h -p 6543 -U postgres Moreno> | psql -h localhost -p 6543 -U postgres Moreno> It presents a double password prompt after I run it: Moreno> Password: Password for user postgres: This is going to prompt once for the remote host's password and twice for the local one (because -C), and the concurrently-running commands are going to be fighting over access to the terminal to do it. Best avoided by using pgpass or non-password-based auth methods. More seriously, you're misunderstanding how -C works. When you use -C, the database you specify to psql (or pg_restore) is NOT the database you're restoring into - the restored db will ALWAYS have the same name as it had when dumped (if that's not what you want then don't use -C). Instead, the database you specify to psql or pg_restore is the database to connect to to issue the CREATE DATABASE command, which should usually be 'postgres'. That explains this bit: Moreno> If I create database (just database, not schema) on target Moreno> machine, I receive the error "database already exists" but Moreno> the dump goes on If I don't create it, I receive the error Moreno> "database does not exist" and processing aborts. -- Andrew (irc:RhodiumToad)
