Thread: database changes during a pg_dump
If I do a
pg_dump -Fc dbname -U postgres > backup.out
to backup a database am I right to assume that any changes made to the database such as a row update that are made while the pg_dump is actually running will be captured in the resulting backup.out file?
Thanks
Austen
Austen Birchall Senior Database Administrator
Met Office FitzRoy Road Exeter EX1 3PB United Kingdom
"Birchall, Austen" <austen.birchall@metoffice.gov.uk> writes:
> If I do a
> pg_dump -Fc dbname -U postgres > backup.out
> to backup a database am I right to assume that any changes made to the database such as a row update that are made
whilethe pg_dump is actually running will be captured in the resulting backup.out file?
No. You'll get a snapshot as of pg_dump's starting time.
There are some corner cases about DDL that's in progress while pg_dump
starts up, but user data will definitely adhere to the snapshot.
regards, tom lane
1. For 9.4.3 on Windows Server 2008 the installer has installed to: C:\Program Files (x86)\PostgreSQL\9.4\data Is this the recommended location 2. Should PGDATA be set as a system variable and if so to what? 3. If I try to try pg_ctl manually I get: C:\Program Files (x86)\PostgreSQL\9.4\bin>pg_ctl -D C:\Program Files (x86)\Post greSQL\9.4\data status pg_ctl: unrecognized operation mode "Files" Try "pg_ctl --help" for more information. C:\Program Files (x86)\PostgreSQL\9.4\bin> What is the correct syntax to do this? Many thanks Austen Austen Birchall Senior Database Administrator Met Office FitzRoy Road Exeter EX1 3PB United Kingdom
QA1 - If you are talking about the program itself, there is nothing bad about that location. If you are talking about the data cluster location (where will be store the data managed by PostgreSQL), it all depends on the expected size of your DB. However, even if your C:\ drive can afford the size of your DB, I would place the data component somewhere else like on another drive or at least not under " C:\Program Files (x86)\..." Something likes C:\mydatacluster would be easier to manage (access, later upgrade...) QA2- It is a good idea to set PGDATA as a system variable. You must set it to actual data location which in your case seems to be C:\Program Files (x86)\PostgreSQL\9.4\data. For instance, mine is set to E:\pgsqlData. QA3- About pg_ctl: unrecognized operation mode "Files" error message, my guess is that you need to quote the location since there are white spaces in it. This version should work... pg_ctl -D "C:\Program Files (x86)\PostgreSQL\9.4\data" status Daniel -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Birchall, Austen Sent: June-16-15 10:15 To: pgsql-novice@postgresql.org Subject: [NOVICE] PGDATA on Windows 1. For 9.4.3 on Windows Server 2008 the installer has installed to: C:\Program Files (x86)\PostgreSQL\9.4\data Is this the recommended location 2. Should PGDATA be set as a system variable and if so to what? 3. If I try to try pg_ctl manually I get: C:\Program Files (x86)\PostgreSQL\9.4\bin>pg_ctl -D C:\Program Files (x86)\Post greSQL\9.4\data status pg_ctl: unrecognized operation mode "Files" Try "pg_ctl --help" for more information. C:\Program Files (x86)\PostgreSQL\9.4\bin> What is the correct syntax to do this? Many thanks Austen Austen Birchall Senior Database Administrator Met Office FitzRoy Road Exeter EX1 3PB United Kingdom -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Following up on Daniel's helpful reply Q1 - In Linux we are moving away from putting version numbers (i.e. 9.4 here) in PGDATA and the whitespace makes things morecomplicated than need be so any further suggestions/thoughts will be useful. Q2 - yes I did this Q3 C:\Program Files (x86)\PostgreSQL\9.4\bin>pg_ctl -D "C:\Program Files (x86)\PostgreSQL\9.4\data" status pg_ctl: no server running C:\Program Files (x86)\PostgreSQL\9.4\bin> Which I a bit confused about! Austen Birchall Senior Database Administrator Met Office FitzRoy Road Exeter EX1 3PB United Kingdom
Q3- I get the same message on standard dos window. You will find the expected answer by opening a command prompt with administrator privileges (Run as administrator - start menu, right click on the dos window icon) Daniel -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Birchall, Austen Sent: June-16-15 11:56 To: Daniel Begin; pgsql-novice@postgresql.org Subject: Re: [NOVICE] PGDATA on Windows Following up on Daniel's helpful reply Q1 - In Linux we are moving away from putting version numbers (i.e. 9.4 here) in PGDATA and the whitespace makes things more complicated than need be so any further suggestions/thoughts will be useful. Q2 - yes I did this Q3 C:\Program Files (x86)\PostgreSQL\9.4\bin>pg_ctl -D "C:\Program Files (x86)\PostgreSQL\9.4\data" status pg_ctl: no server running C:\Program Files (x86)\PostgreSQL\9.4\bin> Which I a bit confused about! Austen Birchall Senior Database Administrator Met Office FitzRoy Road Exeter EX1 3PB United Kingdom -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Birchall, Austen wrote on 16.06.2015 16:15: > 1. For 9.4.3 on Windows Server 2008 the installer has installed to: > > C:\Program Files (x86)\PostgreSQL\9.4\data > > Is this the recommended location Definitely not. I thought the Windows installer was long fixed to suggest an more appropriate location. If it still defaults to "Program Files" I'd call this a bug. (I personally don't use the installer on Windows, only the ZIP binaries) > 2. Should PGDATA be set as a system variable and if so to what? Not necessarily, because you can pass the location when defining the service. > > 3. If I try to try pg_ctl manually I get: > > C:\Program Files (x86)\PostgreSQL\9.4\bin>pg_ctl -D C:\Program Files (x86)\Post > greSQL\9.4\data status > pg_ctl: unrecognized operation mode "Files" > Try "pg_ctl --help" for more information. > > C:\Program Files (x86)\PostgreSQL\9.4\bin> > > What is the correct syntax to do this? > You need double quotes: C:\Program Files (x86)\PostgreSQL\9.4\bin>pg_ctl -D "C:\Program Files (x86)\PostgreSQL\9.4\data status" But again: storing application data in "Program Files" is never a good idea on Windows. %ProgramData% (usually c:\ProgramData) is a _much_ better choice - or anything else _outside_ "Program Files". If you run initdb manually you need to make sure that privileges on the directory or set correctly. This is usually the case for anything outside of "Program Files". Thomas
Q3- I get the same message on standard dos window. You will find the expected answer by opening a command prompt with administrator privileges (Run as administrator - start menu, right click on the dos window icon) Daniel -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Birchall, Austen Sent: June-16-15 11:56 To: Daniel Begin; pgsql-novice@postgresql.org Subject: Re: [NOVICE] PGDATA on Windows Following up on Daniel's helpful reply Q1 - In Linux we are moving away from putting version numbers (i.e. 9.4 here) in PGDATA and the whitespace makes things more complicated than need be so any further suggestions/thoughts will be useful. Q2 - yes I did this Q3 C:\Program Files (x86)\PostgreSQL\9.4\bin>pg_ctl -D "C:\Program Files (x86)\PostgreSQL\9.4\data" status pg_ctl: no server running C:\Program Files (x86)\PostgreSQL\9.4\bin> Which I a bit confused about! Austen Birchall Senior Database Administrator Met Office FitzRoy Road Exeter EX1 3PB United Kingdom -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Hi I have the following db: Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- ***db | postgres | UTF8 | en_US.utf8 | en_US.utf8 | Running on RedHat Linux 6 in the UK ? Trying to restore the data after restoring the tables etc. from a pg_dumpall out.sql type file using pg_restore -d ***db_database_dump201507131900 -v just hangs Is this some kind of conflict between database and host settings? If not any other ideas? Many thanks Austen Austen Birchall Senior Database Administrator Met Office FitzRoy Road Exeter EX1 3PB United Kingdom
Hi
I have the following db:
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
***db | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
Running on RedHat Linux 6 in the UK
?
Trying to restore the data after restoring the tables etc. from a pg_dumpall out.sql type file using
pg_restore -d ***db_database_dump201507131900 -v
just hangs
Is this some kind of conflict between database and host settings?
If not any other ideas?
Many thanks
Austen
Austen Birchall Senior Database Administrator
Met Office FitzRoy Road Exeter EX1 3PB United Kingdom
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Strangely when I tried it previously it listed a few tables but no data was loaded – now it just hangs
Austen
Austen Birchall Senior Database Administrator
Met Office FitzRoy Road Exeter EX1 3PB United Kingdom
Tel: +44 (0)1392 884481 Fax: +44 (0)1392 885681
E-mail: austen.birchall@metoffice.gov.uk Website: http://www.metoffice.gov.uk
From: Payal Singh [mailto:payal@omniti.com]
Sent: 15 July 2015 19:26
To: Birchall, Austen
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] pg_restore hangs for en_US.utf8 in the UK
Since you're using the verbose option, does it hang/stop while restoring a particular table on every try? Anything peculiar about the last couple of line in the verbose output before it hangs?
Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253
On Wed, Jul 15, 2015 at 11:59 AM, Birchall, Austen <austen.birchall@metoffice.gov.uk> wrote:
Hi
I have the following db:
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
***db | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
Running on RedHat Linux 6 in the UK
?
Trying to restore the data after restoring the tables etc. from a pg_dumpall out.sql type file using
pg_restore -d ***db_database_dump201507131900 -v
just hangs
Is this some kind of conflict between database and host settings?
If not any other ideas?
Many thanks
Austen
Austen Birchall Senior Database Administrator
Met Office FitzRoy Road Exeter EX1 3PB United Kingdom
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
"Birchall, Austen" <austen.birchall@metoffice.gov.uk> writes:
> Trying to restore the data after restoring the tables etc. from a pg_dumpall out.sql type file using
> pg_restore -d ***db_database_dump201507131900 -v
> just hangs
pg_dumpall produces a SQL script; you should feed that to psql not
pg_restore.
I'm not sure though why you're seeing it hang; all the versions I have
handy produce some kind of error message when fed a script file.
Hmm ... is that the *whole* command line? If so problem is that it's
patiently waiting for some input on stdin.
regards, tom lane
(As usual) I didn't explain myself very clearly so taking a step back I tried a different approach: 1. Step1 postgres=# update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template0'; 2. Following http://www.postgresql.org/docs/9.4/static/app-pgrestore.html $createdb -T template0 ***db which gives postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- ***db | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | LATIN1 | en_GB | en_GB | template0 | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | LATIN1 | en_GB | en_GB | postgres=CTc/postgres+ | | | | | =c/postgres test | postgres | LATIN1 | en_GB | en_GB | (6 rows) 3. $pg_restore -d ***db_database_dump201507131900 And the tables and data now seem to be back! I must have got confused before as tom lane suggested Thanks for your help Austen -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: 16 July 2015 01:36 To: Birchall, Austen Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] pg_restore hangs for en_US.utf8 in the UK "Birchall, Austen" <austen.birchall@metoffice.gov.uk> writes: > Trying to restore the data after restoring the tables etc. from a > pg_dumpall out.sql type file using pg_restore -d > ***db_database_dump201507131900 -v just hangs pg_dumpall produces a SQL script; you should feed that to psql not pg_restore. I'm not sure though why you're seeing it hang; all the versions I have handy produce some kind of error message when feda script file. Hmm ... is that the *whole* command line? If so problem is that it's patiently waiting for some input on stdin. regards, tom lane
I'm not sure how they done it but our Platforms Team seem to have installed PostgreSQL 9.4.1 in both /var/lib/pgsql/data Which is want I want And /var/lib/pgsql/9.4/data/ Which is want I don't want! For most of our clusters we get: postgres=# SHOW data_directory; data_directory --------------------- /var/lib/pgsql/data (1 row) postgres=# but for a couple we get: postgres=# SHOW data_directory; data_directory --------------------- /var/lib/pgsql/9.4/data (1 row) postgres=# My questions are: 1. How/when is the value of data_directory set - is it set as part of the install? 2. Is it possible to change the value of data_directory without doing a frest install? Thanks Austen Austen Birchall Senior Database Administrator Met Office
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Austen Birchall wrote: > postgres=# SHOW data_directory; > /var/lib/pgsql/data ... > postgres=# SHOW data_directory; /var/lib/pgsql/9.4/data ... > 1. How/when is the value of data_directory set - is it set as part of the install? Yes. It is set when Postgres starts up, and cannot be changed without a restart. The value can be hard-coded in your postgresql.conf file, but usually defaults to the PGDATA environment variable, or the -D option to the 'postgres' program. > 2. Is it possible to change the value of data_directory without doing a frest install? Yes. But I would be careful, it looks like you have may have two active clusters. I'd document everything clearly before proceeding: use "show port" and "show unix_socket_directories" to learn why you can connect to both clusters at the same time, if that is the case. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201507270811 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlW2JGgACgkQvJuQZxSWSsg70wCfYhpgJx5HdynM9OlLzTvuHCxr oDwAoPevJBbQTc9Hw8ioHeKSnyUHTuhQ =3KTG -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Austen Birchall wrote:
> postgres=# SHOW data_directory;
> /var/lib/pgsql/data
...
> postgres=# SHOW data_directory;
/var/lib/pgsql/9.4/data
...
> 1. How/when is the value of data_directory set - is it set as part of the install?
Yes. It is set when Postgres starts up, and cannot be changed without a restart. The value
can be hard-coded in your postgresql.conf file, but usually defaults to the PGDATA
environment variable, or the -D option to the 'postgres' program.
> 2. Is it possible to change the value of data_directory without doing a frest install?
Yes. But I would be careful, it looks like you have may have two active clusters. I'd
document everything clearly before proceeding: use "show port" and
"show unix_socket_directories" to learn why you can connect to both clusters at the
same time, if that is the case.
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201507270811
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAlW2JGgACgkQvJuQZxSWSsg70wCfYhpgJx5HdynM9OlLzTvuHCxr
oDwAoPevJBbQTc9Hw8ioHeKSnyUHTuhQ
=3KTG
-----END PGP SIGNATURE-----
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
--
Amit Sharma
Dear Fellow U.S. Citizen, As described in the letter below, this December, President Obama is planning to make the U.S. a state of a global government and is taking executive action to redistribute what we earn and own to all developing countries worldwide. To keep this from happening, take action as soon as possible! · Please, read the letter below to understand what Obama and the U.N. are plotting to do against us and what your Senators and Representatives can do to stop him. · Forward this email unchanged to everyone you know or otherwise can contact. · On http://www.house.gov/, enter you zip code and press Go, or on http://www.house.gov/representatives/, click the first letter of your state, then click on the link (their name) to your Representative’s website. Navigate to your Representative’s contact page. Copy and paste the letter below into your Representative’s contact page, and send it to your Representative. · On http://www.senate.gov/senators/contact/, choose your state, and click the link to your Senator’s website’s contact page or website. Copy and paste the letter below into your Senator’s contact page, and send it to your Senator. Your future depends on you. Make taking those steps a high priority. Thank you. Here is the text of the letter … | |
SUBJECT: Last Chance to Defend Our Freedom – Impeach and Imprison Obama Being a U.S. citizen, it is my civic duty and honor to write to you regarding a matter of grave national consequences. This December, at the 21st meeting of the U.N. Framework Convention on Climate Change (UN FCCC) Conference of the Parties (COP-21) in Paris, France, President Obama intends to enter the U.S., that is to enter We the People, into a legally-binding agreement. This agreement will make the U.S. a state of a global government; the UN FCCC COP. This global government will make decisions that subvert our national sovereignty and the Constitution of the United States. It will assume control of the global economy; socializing it by monetizing greenhouse gas emission allowances and allocating those allowances to countries worldwide. To developing countries, it will allocate surplus allowances, but to the U.S. it will allocate less than we need to continue our present fossil fuel usage. President Obama and the COP will require We the People to purchase periodically increasing numbers of allowances from developing countries; redistributing increasing amounts of our hard earned money to foreigners through the cap-and-trade scheme that President Obama is imposing on us through executive actions. This will cause the price of every U.S. made product to increase, reduce our purchasing power, and drive us into entitlement programs such as Obama’s Making Work Pay. Furthermore, the COP will require the U.S. to revoke intellectual property rights of U.S. industries, businesses, and citizens, to redistribute our trade secrets and patents to developing countries where it will be used to create industry and businesses that will take away our jobs and dignity; also forcing us into financial hardship, heartache, and entitlement programs. When natural disasters occur in developing countries, instead of the citizens of those countries staying and rebuilding, the COP will require the U.S. to take them in as environmental refugees; giving them our land for compensation for their loss, and increasing the number of people competing for our jobs and being given our tax dollars through entitlement programs. Also, if all these actions and others cause the U.S. economy to crash, developing countries and the COP will continue to take what is ours with heartless disregard for our welfare. Obama’s and the UN FCCC’s plans against us are criminal, malevolent, and unconscionable. When President Obama enters We the People into that legally-binding agreement with the UN FCCC, you shall take action. First, Senators, for the sake of our nation and to defend our future, reject that legally-binding agreement with the UN FCCC. Second, for waging political and economic warfare against We the People, that is for signing that agreement which will oppress us under a global government and for imposing a cap-and-trade scheme on us which will redistribute our money to the developing world, charge President Obama with treason according to Article III. Section 3. of the Constitution and with high crimes according to Article III. Section 4. of the Constitution. Representatives, impeach President Obama according to Article I. Section 2. of the Constitution and Senators, try his impeachment and impeach him according to Article I. Section 3. of the Constitution. Third, charge former President Obama with one count of capital theft for each U.S. citizen whose hard-earned income has already been stolen to fund green energy, cooperative R&D, and other projects in foreign countries and will be stolen under the UN FCCC and through Obama’s and the UN FCCC’s cap-and-trade scheme; so that he may be indicted, tried, judged, and incarcerated for multiple lifetime sentences without any opportunity for parole. You have a decision to make regarding your service to your state constituency. Will you violate your oath to defend the Constitution of the United States from all enemies foreign and domestic, ratifying its subversion and that of our sovereignty, or will you reject further actions subverting it; defending our Constitution and our right to freedom and self-governance? Will you impose untold hardship and despair on We the People, or will you bring relief and restore hope? Will you be an accomplice to the plundering of our money, trade secrets, patents, industry, businesses, jobs, and land, or will you protect us from fraud and theft; rejecting any legally-binding agreement with the UN FCCC, impeaching President Obama, and charging him for his crimes against We the People? |
|