Database Ops
CC Attribution - NonCommercial - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this
Sakamoto, M.
Easy and Effective Operation for production systems with PostgreSQL NTT (Nippon Telegraph and Telephone) group has made effort to introduce PostgreSQL to its production systems that are large and mission-critical. Introducing PostgreSQL, we found it may be an obstacle that operation tools for PostgreSQL are not provided enough. So we have developed tools for backup, data load, and performance monitoring. In the talk, we will introduce these tools and how to improve database operation using them. NTT group, which is the largest telecommunication career in Japan providing more than 120 million subscribers, has made effort introducing PostgreSQL to its production systems that support telecommunication. When we apply PostgreSQL to a production system, we will have to do many 'house-keeping chores'; At the beginning, you will have to load initial data to PostgreSQL, just after starting operation, you will have to periodically take back up files against data loss caused by media crash. Many proprietary DBMS provide operational and/or management tools to make DBA's work easier and more efficient. Additionally, such a tool provides an easy and standard way of oprerations, it enables not a skilled engineer to manage database systems well. Concerning PostgreSQL, such tools are not enough provided, it may be an obstacle to introduce PostgreSQL into enterprise systems. So we have developed some operational tools for taking back up, loading data and monitoring performance. And we provide technical know-hows about appropriate operations so that an engineer who is not familiar with PostgreSQL as we expect can manage PostgreSQL well. The talk will introduce our daily operation activities with assistance of the tools; pgrman for taking backups, pgbulkload for data loading with data cleansing, and pg_statsinfo for performance monitoring. Talking things above we hope to share PostgreSQL operation know-hows with many DBAs.
 Hello everybody. I'm Sakamoto from NTT. I will show you some demos about R2s. So first off, we have been developing number of tools to enhance possible SQL. Today we focus on the operation, picking up four tools, PG Start Info, PG Online, PG New York, PG Backroad. So let's start with the PG Start Info. It visualizes databases' static fix. You can get visualized summary of your instance via web browser. No special knowledge on system catalogs, such as PG Start IO tables, or PG Recentises, or PG Blah Blah Blah. And no maintenance tasks are required to use PG Start Info. When the PostgreSQL instance starts, the agent modules also starts. And when you stop the PostgreSQL instance, the agent modules also stops. So no additional maintenance tasks are required to use PG Start Info. You can get dynamic HTML reports via web browser. This is a screenshot of the report. This is a pie chart of table sizes table. And this is disk-reads table. And this is a time series of the database sizes. The report includes more useful information, such as most issued queries from PG Start statements, and checkpoint static fix from probably the server logs, and auto-bacuum static fix from the server logs also. So I can start. I can demonstrate. Let me see you. I have running the Linux on this laptop, so I can get the browser. And I get the summary, such as PostgreSQL version and database encodings. And the database lists and the sizes. More detailed reports are available here. And here. This is a list of snapshots. Snapshot is a collection of the instances static fix, such as PG Start statements information, or PG Relation Size Information. So specifying the two snapshots. And I can get the dynamical HTML report during this time period. It includes database static fix as a visualized graph time series. This is transaction static fix. Number of commits, number of rollbacks are shown. This is database size. And instance processes, disk usage part table spaces, disk usage part tables. This is a pie chart. And long transactions from the PG Start statement and heavily updated tables from the PG Start IOTables. Do you have hot updates versus do you have hot updates numbers here? Or comparison, how many were, what how many were disk updates? How many? Sorry? Do you have statistics on hot updates versus regular updates? Oh, yeah. Yes. This column shows a hot update ratio. And this is categorized by the scale types, insert, update, delete. Heavy access tables, low density tables. It is very important information for you to execute backing for. Check point activity from the server logs, auto backing activities, function calls, static fix, and more, server settings. So you can get a visual summary of your instances very easily. This is the first demo. The next one, PG-REO. PG-REO reorganizes a table without backing. When you issue a backingful command or a index command or a class segment, it blocks other concurrent queries. On the other hand, PG-REO doesn't work. PG-REO reorganizes, recreates the table and indexes. So the table sizes shrinks. So PG-REO is a good alternative of backing for cluster in unstoppable systems. Now let me show you. I have made an application, the scope application. This is an pre-referent. This is a front. Insert a row every two seconds. Of course, inserts were blocked by the log. When I lock the table, the elephant inserts the table. PG-Pinch accounts. So I will lock the table so the elephants get the arrow, and the inserts were blocked. Of course, the log will be released. Say, I issue a commit command. The inserts were successful. OK, now I issue a backingful command. I issue a backingful PG-Pinch account so the elephant gets blocked until I cancel the query. But when I issue a PG-REO command, other concurrent queries does not block. Yeah, it's not blocked. Before we issue a PG-REO command, we should check the database sizes of the table. OK, this is 61 megabytes. Now I issue a PG-REO command. Oops, OK. OK, OK. Inserts are not never blocks. And the organization is successful. So let me check the database sizes again. OK, it shrinks. I have generated 10% of updates. So the shrinks size is OK. What is the application that's running on the table? This is just a 100 line of Python using the Python using the QT bindings. It's just 100 line tools. You have that posted somewhere? Because that's actually kind of cool. OK, this is the second demonstration. OK, next go to the third one. Third one is a PG-R command. This simplifies backup and PIT operations. I like the contrast to PG-based backup and PG-archive command. This tool also simplifies backup and PIT operations. Everything related to backup is just a one line operation when using PG-R men. It includes full backups, archived backups, deleting all the backups, and deleting unnecessary archived works and everything. Especially managing generations and incremental backups outstanding features. When I issue PG-R men command, the full backup is going into the PG-R men archives. And when you want to restore, it is just one line operation. OK. OK. OK. OK. I will shut down the post-quad skewer immediately. Oh, sorry. I first backup. Set the environment. And one line execution. PG-R men internally issues a PG-start backup and PG-stop backup checks the instance has the table spaces. If the table spaces exist, and this command also looks into the table spaces and also backups, everything is OK. You can see the backup mode equal full. This is a full backup. And with server log, it also backups not only the PG data, but also the server logs. Backup is still not set. You said the backup is set and the server logs. Server logs. Server logs. OK. It's gone. It's finished. So I see the backup is listed. OK. This line is the full backup. The time, the day time and the backup size are listed. And the status is also listed. When I issue a PG-R man validate command, it checks the CRC command and the status changes to OK. This CRC is used when you restore this data. You can also backup with incremental mode in the same syntax and archive mode. It also is in one line. Check the validator and show. Everything is OK. Incremental backups and archive backups are very little size. What if this error is the status? Yeah. Probably this is... I didn't learn the instances probably. This is yesterday. Last month's demonstrations. This is an error. Probably I didn't learn the instances. So these are different backups over time? Yeah. This is today's... Today's... Today's ones. OK. Do you do it with the backup or the backup or the backup? I'm sorry? Do you backup the different ones to the ongoing operations to a backup? Yeah. How do you do this while you're doing the backup? Are you using the PNG? You don't have the ongoing ones because the backup is... It's an online backup, not a PG dump. Not a PG dump? It is an online backup supporting the PITR recovery. That way, you don't have to run any account with the ongoing... Oh, yeah....operational... OK. Update and insert everything is... OK. During the... So it's calling a PTCGP backup and doing a binary backup? So it's not working? How is it working? No, not working. No luck? No luck. Sorry? Sorry? Yeah. Yeah. Full database. Full data is stored in the... No, the results of the hard-match job, this stuff here... Yeah, what you're showing right here. The start time of the backups, the results... Is that in a table in the database as well, or is it stored in some of the cells? When you do a PG hard-match show, where is it getting this data? Oh, yeah. It is stored in the environment. I set the environment PG-R-man catalogs. I can get... Got it. OK. And full backup is absolutely compatible as a result of PG start backup. So you can restore the result R-man. Probably today's backup cell... In this directory. I've done three backups, so three directorizations. If you restore the input for the backup, would your tool work up from the last full backup? Yeah, yeah, yeah. OK. Thanks, Chad. The question... You did a backup while you got this little program running in the back that sent it in rows into the database, right? You have this little program here running inserting rows into the database while it was running at around 140 into the backup. Now, let's say the database fails now. Still is working. You can do that next. Yeah, yeah. You can concurrently insert it. And the list of point is probably there. The physical backup will guarantee you a consistent picture of the database. Because in what's the time of where it's started in the wallbox. So, right, depending on when you start the backup, some of these inserts will be there in the recovery. Right. And so what I imagine is that if that process fails, and you do a recover from the backup, when you restart that program, it'll start up that app from what's word stuff down. It's just a program that's inserting rows into the database and has a low count. So, it won't pay you. I want you to... But you have to do some kind of a roll forward in order to get... Yeah, you won't... I mean, you'll have your backup from the point you started in the physical backup, but you won't have stuff that's been inserted after that point in time. Right. So, you can do some. The program will have to handle fail-free. Does PGR man support using archive, wall archiving? Yeah. So, if you set that up, you can use this in conjunction with that to basically get a consistent backup. Okay. So, you could grab your last incremental backup and then go to whatever wall logs you've got and keep going forward in the back. Yes. Does it... Would you do that calling PGR man from the archive command setting? Is there... Do you provide a script to this tablet that or do you have to kind of set that up yourself for the... Sorry. The coefficient is... How can you continue to backup wall segments after you ask for it to get full backup? Because the full backup is the online backup, so it is a dynamic one. So, we are not sure how we can backup the wall after that. Ah. Ah. Over here, PGR man... In the PGR man syntax, when you issue PGR man restore, the necessary archive log will restore. See, you do it on the restore side. Okay. What about compression backups? Did it compress the backups? Ah, no. Okay. So, I stopped the instances so the elephants get that. Okay. P. I will delete the full databases. Ouch. There is no best directory. PGR man restore, such as the latest full backups and applies incremental backup and pull out the archive log to the proper point. And generate... Automatically generates recovery conf. Here, base is restored and recovery conf is also generated. So, all you have to do is just PGR control start to restore the full databases. What... Recent buttons are okay probably at least 8.3 or higher is okay. Is it possible to backup only one database with the new cluster or does it backup all databases? Four databases. PGR man uses online backup. When you use online backups, the full database. Okay. The last one. This is PGR workload. The problem is copy command. Copy command just succeeds or not. Which makes it very difficult to load a huge number of rows with a little portion of more form rows. PGR workload solve this problem. When PGR workload finds some more form rows, it puts them to the bad files and process loading. So, 99% of valid rows are inserted successfully and less. 0.1% of more form rows are extracted to the bad files. It is very small text file. So, you can edit the... You can fix the data and you can be load the data. Okay. This is an example. There is a CSV file. Sample CSV. Japanese characters. This is a Japanese character. This is a comma separated files with three rows. Which results in the two comma. But the third line, this is only one comma. This is a malform data. So, you cannot load this data by copy command. PGR workload using this control file. In the control file, you can specify the bad file, file pass, and other options. The input file name and the table name of the load. Type, quote, escape, and delimiter. And we can specify the limit. The number of the total rows. I specify this three. So, I can load using them by specifying this control file. Okay. I specify three rows loaded. So, three rows successfully loaded. And one rows malformed it. So, this is pass error. How do you handle null characters? You have to wait and describe them in the follow-up file. Is it in the copy command you can say a serving character means null? Nulls. No. How do you write null value? Null. Null value. In your control file? Yeah. You can also specify the null value. Today, I did not register the bad. So, let me see the bad file. Bad file takes. Yeah. This line is extracted. So, the load is very easy using pg barcode. This is the last demo. Okay. Other, we have developing another tools also. Almost feature available as open source software. Please try them and please let me get feedback. Thank you for listening.
 Hello everybody.  I'm Sakamoto from NTT.  I will show you some demos about R2s.  So first off, we have been developing number of tools  to enhance possible SQL.  Today we focus on the operation, picking up four tools,  PG Start Info, PG Online, PG New York, PG Backroad.  So let's start with the PG Start Info.  It visualizes databases' static fix.  You can get visualized summary of your instance via web browser.  No special knowledge on system catalogs,  such as PG Start IO tables, or PG Recentises,  or PG Blah Blah Blah.  And no maintenance tasks are required to use PG Start Info.  When the PostgreSQL instance starts,  the agent modules also starts.  And when you stop the PostgreSQL instance,  the agent modules also stops.  So no additional maintenance tasks  are required to use PG Start Info.  You can get dynamic HTML reports via web browser.  This is a screenshot of the report.  This is a pie chart of table sizes table.  And this is disk-reads table.  And this is a time series of the database sizes.  The report includes more useful information,  such as most issued queries from PG Start statements,  and checkpoint static fix from probably the server logs,  and auto-bacuum static fix from the server logs also.  So I can start.  I can demonstrate.  Let me see you.  I have running the Linux on this laptop,  so I can get the browser.  And I get the summary, such as PostgreSQL version  and database encodings.  And the database lists and the sizes.  More detailed reports are available here.  And here.  This is a list of snapshots.  Snapshot is a collection of the instances static fix,  such as PG Start statements information,  or PG Relation Size Information.  So specifying the two snapshots.  And I can get the dynamical HTML report  during this time period.  It includes database static fix as a visualized graph time  series.  This is transaction static fix.  Number of commits, number of rollbacks are shown.  This is database size.  And instance processes, disk usage part table spaces,  disk usage part tables.  This is a pie chart.  And long transactions from the PG Start statement  and heavily updated tables from the PG Start IOTables.  Do you have hot updates versus do you have hot updates numbers  here?  Or comparison, how many were, what how many were disk updates?  How many?  Sorry?  Do you have statistics on hot updates  versus regular updates?  Oh, yeah.  Yes.  This column shows a hot update ratio.  And this is categorized by the scale types, insert, update,  delete.  Heavy access tables, low density tables.  It is very important information for you to execute  backing for.  Check point activity from the server logs, auto backing  activities, function calls, static fix, and more,  server settings.  So you can get a visual summary of your instances very  easily.  This is the first demo.  The next one, PG-REO.  PG-REO reorganizes a table without backing.  When you issue a backingful command or a index command  or a class segment, it blocks other concurrent queries.  On the other hand, PG-REO doesn't work.  PG-REO reorganizes, recreates the table and indexes.  So the table sizes shrinks.  So PG-REO is a good alternative of backing for cluster  in unstoppable systems.  Now let me show you.  I have made an application, the scope application.  This is an pre-referent.  This is a front.  Insert a row every two seconds.  Of course, inserts were blocked by the log.  When I lock the table, the elephant inserts the table.  PG-Pinch accounts.  So I will lock the table so the elephants get the arrow,  and the inserts were blocked.  Of course, the log will be released.  Say, I issue a commit command.  The inserts were successful.  OK, now I issue a backingful command.  I issue a backingful PG-Pinch account  so the elephant gets blocked until I cancel the query.  But when I issue a PG-REO command,  other concurrent queries does not block.  Yeah, it's not blocked.  Before we issue a PG-REO command, we should check the  database sizes of the table.  OK, this is 61 megabytes.  Now I issue a PG-REO command.  Oops, OK.  OK, OK.  Inserts are not never blocks.  And the organization is successful.  So let me check the database sizes again.  OK, it shrinks.  I have generated 10% of updates.  So the shrinks size is OK.  What is the application that's running on the table?  This is just a 100 line of Python using the Python  using the QT bindings.  It's just 100 line tools.  You have that posted somewhere?  Because that's actually kind of cool.  OK, this is the second demonstration.  OK, next go to the third one.  Third one is a PG-R command.  This simplifies backup and PIT operations.  I like the contrast to PG-based backup and PG-archive  command.  This tool also simplifies backup and PIT operations.  Everything related to backup is just a one line  operation when using PG-R men.  It includes full backups, archived backups,  deleting all the backups, and deleting unnecessary  archived works and everything.  Especially managing generations and incremental backups  outstanding features.  When I issue PG-R men command, the full backup  is going into the PG-R men archives.  And when you want to restore, it is just one line operation.  OK.  OK.  OK.  OK.  I will shut down the post-quad skewer immediately.  Oh, sorry.  I first backup.  Set the environment.  And one line execution.  PG-R men internally issues a PG-start backup and PG-stop  backup checks the instance has the table spaces.  If the table spaces exist, and this command also  looks into the table spaces and also backups, everything is OK.  You can see the backup mode equal full.  This is a full backup.  And with server log, it also backups not only the PG data,  but also the server logs.  Backup is still not set.  You said the backup is set and the server logs.  Server logs.  Server logs.  OK.  It's gone.  It's finished.  So I see the backup is listed.  OK.  This line is the full backup.  The time, the day time and the backup size are listed.  And the status is also listed.  When I issue a PG-R man validate command, it checks the CRC command  and the status changes to OK.  This CRC is used when you restore this data.  You can also backup with incremental mode in the same syntax  and archive mode.  It also is in one line.  Check the validator and show.  Everything is OK.  Incremental backups and archive backups are very little size.  What if this error is the status?  Yeah.  Probably this is...  I didn't learn the instances probably.  This is yesterday.  Last month's demonstrations.  This is an error.  Probably I didn't learn the instances.  So these are different backups over time?  Yeah.  This is today's...  Today's...  Today's ones. OK.  Do you do it with the backup or the backup or the backup?  I'm sorry?  Do you backup the different ones to the ongoing operations  to a backup?  Yeah.  How do you do this while you're doing the backup?  Are you using the PNG?  You don't have the ongoing ones because the backup is...  It's an online backup, not a PG dump.  Not a PG dump?  It is an online backup supporting the PITR recovery.  That way, you don't have to run any account with the ongoing...  Oh, yeah. ...operational...  OK.  Update and insert everything is... OK.  During the...  So it's calling a PTCGP backup and doing a binary backup?  So it's not working?  How is it working?  No, not working.  No luck?  No luck.  Sorry?  Sorry?  Yeah.  Yeah.  Full database.  Full data is stored in the...  No, the results of the hard-match job, this stuff here...  Yeah, what you're showing right here.  The start time of the backups, the results...  Is that in a table in the database as well, or is it stored in some of the cells?  When you do a PG hard-match show, where is it getting this data?  Oh, yeah.  It is stored in the environment.  I set the environment PG-R-man catalogs.  I can get...  Got it. OK.  And full backup is absolutely compatible as a result of PG start backup.  So you can restore the result R-man.  Probably today's backup cell...  In this directory.  I've done three backups, so three directorizations.  If you restore the input for the backup, would your tool work up from the last full backup?  Yeah, yeah, yeah. OK.  Thanks, Chad.  The question...  You did a backup while you got this little program running in the back that sent it in rows into the database, right?  You have this little program here running inserting rows into the database while it was running at around 140 into the backup.  Now, let's say the database fails now.  Still is working.  You can do that next.  Yeah, yeah.  You can concurrently insert it.  And the list of point is probably there.  The physical backup will guarantee you a consistent picture of the database.  Because in what's the time of where it's started in the wallbox.  So, right, depending on when you start the backup, some of these inserts will be there in the recovery.  Right.  And so what I imagine is that if that process fails,  and you do a recover from the backup, when you restart that program,  it'll start up that app from what's word stuff down.  It's just a program that's inserting rows into the database and has a low count.  So, it won't pay you.  I want you to...  But you have to do some kind of a roll forward in order to get...  Yeah, you won't...  I mean, you'll have your backup from the point you started in the physical backup,  but you won't have stuff that's been inserted after that point in time.  Right.  So, you can do some.  The program will have to handle fail-free.  Does PGR man support using archive, wall archiving?  Yeah.  So, if you set that up, you can use this in conjunction with that to basically get a consistent backup.  Okay.  So, you could grab your last incremental backup and then go to whatever wall logs you've got  and keep going forward in the back.  Yes.  Does it...  Would you do that calling PGR man from the archive command setting?  Is there...  Do you provide a script to this tablet that or do you have to kind of set that up yourself for the...  Sorry.  The coefficient is...  How can you continue to backup wall segments after you ask for it to get full backup?  Because the full backup is the online backup, so it is a dynamic one.  So, we are not sure how we can backup the wall after that.  Ah.  Ah.  Over here, PGR man... In the PGR man syntax, when you issue PGR man restore, the necessary archive log will restore.  See, you do it on the restore side.  Okay.  What about compression backups?  Did it compress the backups?  Ah, no.  Okay.  So, I stopped the instances so the elephants get that.  Okay.  P.  I will delete the full databases.  Ouch.  There is no best directory.  PGR man restore, such as the latest full backups and applies incremental backup and pull out the archive log to the proper point.  And generate... Automatically generates recovery conf.  Here, base is restored and recovery conf is also generated.  So, all you have to do is just PGR control start to restore the full databases.  What...  Recent buttons are okay probably at least 8.3 or higher is okay.  Is it possible to backup only one database with the new cluster or does it backup all databases?  Four databases. PGR man uses online backup.  When you use online backups, the full database.  Okay.  The last one.  This is PGR workload.  The problem is copy command.  Copy command just succeeds or not.  Which makes it very difficult to load a huge number of rows with a little portion of more form rows.  PGR workload solve this problem.  When PGR workload finds some more form rows, it puts them to the bad files and process loading.  So, 99% of valid rows are inserted successfully and less.  0.1% of more form rows are extracted to the bad files.  It is very small text file.  So, you can edit the...  You can fix the data and you can be load the data.  Okay.  This is an example.  There is a CSV file.  Sample CSV.  Japanese characters.  This is a Japanese character.  This is a comma separated files with three rows.  Which results in the two comma.  But the third line, this is only one comma.  This is a malform data.  So, you cannot load this data by copy command.  PGR workload using this control file.  In the control file, you can specify the bad file, file pass, and other options.  The input file name and the table name of the load.  Type, quote, escape, and delimiter.  And we can specify the limit.  The number of the total rows.  I specify this three.  So, I can load using them by specifying this control file.  Okay.  I specify three rows loaded.  So, three rows successfully loaded.  And one rows malformed it.  So, this is pass error.  How do you handle null characters?  You have to wait and describe them in the follow-up file.  Is it in the copy command you can say a serving character means null?  Nulls.  No.  How do you write null value?  Null.  Null value.  In your control file?  Yeah.  You can also specify the null value.  Today, I did not register the bad.  So, let me see the bad file.  Bad file takes.  Yeah.  This line is extracted.  So, the load is very easy using pg barcode.  This is the last demo.  Okay.  Other, we have developing another tools also.  Almost feature available as open source software.  Please try them and please let me get feedback.  Thank you for listening.
Database_Ops_0_131.png Database_Ops_132_3635.png Database_Ops_3636_3719.png Database_Ops_3720_4367.png Database_Ops_4368_4583.png Database_Ops_4584_4703.png Database_Ops_4704_5507.png Database_Ops_5508_5867.png Database_Ops_5868_6071.png Database_Ops_6072_6251.png Database_Ops_6252_6359.png Database_Ops_6360_6587.png Database_Ops_6588_6671.png Database_Ops_6672_6731.png Database_Ops_6732_8183.png Database_Ops_8184_8471.png Database_Ops_8472_8543.png Database_Ops_8544_8687.png Database_Ops_8688_9179.png Database_Ops_9180_9227.png Database_Ops_9228_11003.png Database_Ops_11004_15023.png Database_Ops_15024_15155.png Database_Ops_15156_15215.png Database_Ops_15216_15647.png Database_Ops_15648_15695.png Database_Ops_15696_20027.png Database_Ops_20028_20147.png Database_Ops_20148_20723.png Database_Ops_20724_21275.png Database_Ops_21276_21395.png Database_Ops_21396_21443.png Database_Ops_21444_26063.png Database_Ops_26064_32951.png Database_Ops_32952_33419.png Database_Ops_33420_33515.png Database_Ops_33516_33635.png Database_Ops_33636_33899.png Database_Ops_33900_36431.png Database_Ops_36432_36539.png Database_Ops_36540_38243.png Database_Ops_38244_38291.png Database_Ops_38292_40835.png Database_Ops_40836_42215.png Database_Ops_42216_43679.png Database_Ops_43680_44147.png Database_Ops_44148_44963.png