Ch. 3 Administration
creating a group role called adminrole that has the various admin privileges
What is the below doing? CREATE ROLE adminrole CREATEDB CREATEROLE;
We could also grant the employee edit access and query access on the customer table
We could also grant the employee edit access and query access on the customer table like: GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO employee_role;
A
What type of a backup would create a full copy of your entire data set? a.) Full backup b.) Differential backup c.) Nightly backup d.) Incremental backup
CREATEROLE
______ attribute allows you to create new roles, ALTER roles and DROP other roles.
honeypots
________ that are used to help mislead intruders about certain resources being available. While the intruders are focused on those systems, this can give the organization enough time to react to protect the actual systems
Bitmap index
this type of index uses a bit array of zeros and ones. These are useful to represent a value or condition that may be true or false
the pg_dump is the command line tool The -U adminrole specifics the user role that will be used to connect to the database
what is pg_dump and -U adminrole in the below? pg_dump -U adminrole -W -F p mydb > c:\backup\mydb.sql
F. BEGIN; UPDATE customer_account SET balance = balance - 500 WHERE account_id = 1000; UPDATE store_inventorySET quantity = quantity - 1WHERE store_id = 5 AND product_name = 'Computer'; INSERT INTO customer_order(account_id, product_name,store_id, quantity,cost) VALUES (1000, 'Computer',5, 1, 500); UPDATE store_accountSET balance = balance + 500 WHERE store_id = 5;
T or F? Each SQL statement does not need to end with a semicolon to separate each out individually in a transaction
-s
The __ will dump out just the object definitions like the tables rather than including the data.
pg_restore
The _________ command is a utility that allows us to restore a PostgreSQL database from an archive that has been created using the pg_dump command using one of the non-plain text formats such as a tar. This command will execute the commands to reconstruct the database to the time in which the database backup was created.
-a
The __ option will only dump out the data but not the schema with the data definitions like the table structure.
pg_dumpall
There is also the _______ command if you wanted to dump out all of the databases within a server.
B
Which is an advantage of only the command line to backup and restore the database? a.)We can encode the backup file to another format. b.)We can restore a remote database. c.)We can backup the data, schema or both at the same time. d.)We can execute backups and restore without knowing the exact syntax.
-d
dumps files into a directory
C.
Applications should be using the superuser role to connect to the database if it contains admin-related tasks. a.)Frequently, there are only some instances where this is not acceptable. b.)Rarely, this is acceptable but we have to be careful. c.)No, this should never be done. d.)Yes, this is always acceptable.
consistency
Data is in a consistent state when a transaction starts and when it ends. For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.
The pg_dump tool will be able to output all of the contents of all database objects into a single file the pg_dumpall command if you wanted to dump out all of the databases within a server. This is not a commonly used option as you typically will only want to back up specific databases at a time
Difference between pg_dump and pg_dumpall
The psql command will restore plain SQL script files that have been created by pg_dump and pg_dump tools. The pg_restore command is a utility that allows us to restore a PostgreSQL database from an archive that has been created using the pg_dump command using one of the non-plain text formats such as a tar NOTE WE USE "<" NOT ">" psql mydatabase < backup.sql pg_restore -f backup.sql postgres
Difference between psql and pg_restore
permissions
For many databases, there is a separation between user accounts and groups. User accounts would be created and added to groups. For best practices, _______ are set to a group so that the users are added to a group. The permissions that are added to the group would be applied to the account.
allows users in the employee_role to use SELECT on employee table but will not allow changes
GRANT SELECT ON employee TO employee_role;
If we wanted to grant access to specific columns, we would add those columns in round brackets after the privilege type
GRANT UPDATE(unit_price) ON track TO employee_role;
GRANT
If we wanted to give the adminrole role to the myadmin user role what is missing? CREATE ROLE myadmin LOGIN PASSWORD 'mypassword'; CREATE ROLE adminrole CREATEDB CREATEROLE; ______ adminrole TO myadmin;
Roles
In PostgreSQL, we use ____ for both users and groups.
CREATE ROLE myadmin LOGIN PASSWORD 'mypassword'; CREATE ROLE adminrole CREATEDB CREATEROLE; GRANT adminrole TO myadmin; REVOKE adminrole_db FROM myadmin;
In sql create a user with a password, create a database and then grant the user access to that database and then revoke it
A.
In the following scenario, which of the following statements would be saved to the database assuming these are all in a single transaction? ROLLBACK 1. Update 2. InsertCOMMIT 3. Insert 4. Delete 5. Update ROLLBACK a.)1, 2 b.)none c.)2 d.) 3, 4, 5
.D In order to detect for SQL injections, the first step should always be to validate the input from the users. It is important to identify what type of content is being sent in.
One of the most common approaches to detect SQL injections is to ensure that the application __________ the data that's received by the user before it sends it to the database? a.)encrypts b.)authorizes c.)encapsulates d.)validates
-t
The __ will only dump out specific tables that match the table name that is passed like __ employee would only dump out the employee table. The __ with a capital _ will dump out all tables other than the ones that are listed. Can output to tar which will create an archive file similar to a .zip file
-C
The __ with a capital letter will start the output with a command to create the database and reconnect to the created database. This option allow you to avoid having to create the database first
psql
The ____ command will restore plain SQL script files that have been created by pg_dump and pg_dump tools
CONNECTION LIMIT
The _____ ____determines how many concurrent connections the role can make. The default is set to -1 meaning that there is no limit.
pg_dump
The _______ tool will be able to output all of the contents of all database objects into a single file. The script dumps contain SQL commands that are in plain-text files that can be used to reconstruct the database back to the state that it was in when the database was backed up.
B The b-tree index is used for pattern matching as long as the pattern is constant and starts with the beginning of the string being set
The following type of index is generally used for pattern matching operators such as LIKE 'bob%'. a.)GIN b.) B-tree c.)BRIN d.) Hash
isolation
The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized. For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.
Transaction
The purpose of a ________ is to combine multiple SQL statements together into a scenario that would either execute all of the statements or none of them
Generalized Inverted Index (GIN)
This is a unique type of index for PostgreSQL that focuses on indexing array values and testing if a value exists. Operators that you would see with this would be <@, @>, = and &&.
A. To protect any sensitive data in the database like credit card information or personally identifiable information, the columns should be encrypted.
To protect sensitive data, you must hide it through? a.) Encryption b.) Privileges c.) Roles d.) Decryption
BEGIN; UPDATE customer SET company='Telus', support_rep_id=6 WHERE customer_id = 9; UPDATE customer SET phone='9991112222', email='[email protected]' WHERE first_name = 'Daan' AND last_name='Peeters'; COMMIT;
Use a transaction to update the customer table to set company to Telrus and support_rep_id = 6 where customer_id = 9. then update customer and set the phone number = 999111222 and email = '[email protected]' where first_name is Daan and last name is Peeters
A.
Users can be given superuser access to connect to the database. a.)No, this should never be done. b.)Yes, this is always acceptable. c.)Frequently, there are only some instances where this is not acceptable. d.)
A. The superuser role should never be used to connect to our application as it has full power of the user running it has. There are significant risks if this is provided.
Users should be granted an explicit set of powerful permissions that are needed regardless of how complex rather than providing superuser access directly. a.)Yes, this should always be done. b.)Rarely, this is acceptable but we have to be careful. c.)No, this should never be done. d.)Most of the time, there are only some instances where this is not acceptable.
The -W will prompt the pg_dump command to prompt for the password on adminrole before it can continue. The -F specifics the output file format. In this case, the p stands for plain-text SQL script file
What do -W and -F p do? pg_dump -U adminrole -W -F p mydb > c:\backup\mydb.sql
We indicate the database that we want to backup which is mydb. The > c: :\backup\mydb.sql is the output backup file name and path that we are backing up to.
What do mydb and > c:\backup\mydb.sql do? pg_dump -U adminrole -W -F p mydb > c:\backup\mydb.sql
D.
What is an advantage of only the GUI to backup and restore the database? a.)We can back up a remote server. b.)We can run the backups in batch. c.)We can back up multiple databases at once. d.)We can choose different interfaces based on the database to use.
; after begin
What is missing from the below transaction? BEGIN UPDATE customer_account SET balance = balance - 500 WHERE account_id = 1000; UPDATE store_inventorySET quantity = quantity - 1WHERE store_id = 5 AND product_name = 'Computer'; INSERT INTO customer_order(account_id, product_name,store_id, quantity,cost) VALUES (1000, 'Computer',5, 1, 500); UPDATE store_accountSET balance = balance + 500 WHERE store_id = 5; COMMIT;
Consistency.... unlike the atomicity, the issue was not caused by an error in the database statement.
What is the below an example of? Let us say that during the second UPDATE statement, the system had a failure. When the system recovers, the transaction would have only partially executed. In this situation, the system would roll back those UPDATE statements to the consistent state prior to the transaction starting. Otherwise, we would have a problem with the data being inconsistent.
CONCURRENTLY
When we run the DROP INDEX statement, PostgreSQL will get a lock on the table and block any other access to the table until the dropping of the index is complete. If there is a conflicting transaction that is running on the table, we can add ___________ to the command to wait until any conflicting transactions are done before we remove the index.
C.
Which is an advantage of only the command line to backup and restore the database? a.)We can continue to restore a database even if there are errors in the file. b.)We can stop restoring the database if there is an error. c.)We can backup from one database and immediately send the result to restore another database. d.)We can choose different interfaces based on the database to use.
A
Which of the following DROP INDEX statements would drop the index without locking on select, insert, update, and delete statements that are running at the same time on the index's table? a.)DROP INDEX CONCURRENTLY myindex; b.)DROP INDEX myindex; c.)DROP INDEX myindex CASCADE; d.)DROP INDEX IF EXISTS myindex;
atomicity, consistency, isolation, and durability.
ACID Properties
durability
After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure. For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.
atomicity
All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are. For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.
D. Atomicity requires that all SQL requests in a transaction should be fully completed and if not, the entire transaction should be aborted. The transaction should be viewed as a single logical unit of work that is indivisible.
Which of the following criteria is specific to the atomicity property? a.)In the event of system failure, no transactions that were done should be undone. b.)If any of the transaction parts violates an integrity constraint, the entire transaction must be aborted. c.)Data used in one transaction cannot be used in another transaction until the first transaction is completed. d.)A transaction should be treated as a single logical unit of work that is indivisible.
B-tree
Which of the following index is the most common type of index? a.)GIN b.) B-tree c.)BRIN d.) Hash
D.
Which of the following scenarios reflect the atomicity property? A.1. Randall has updated the product table to set the price of an item to $99.2. The database fails after Randall commits the data.3. After the database starts back up, Randall checks the product and the price is set to $99 for the product he updated. B.1. A user attempts to do a bank transfer between accounts.2. Money withdrawn from the first account is performed.3. Only once the money is verified to have been withdrawn, the money is deposited into the second account.4. Verification is done again to ensure that the total amounts before and after the transaction are maintained. C.1. Transaction 1 updates customer 1's address.2. Transaction 1 updates customer 2's address.3. Transaction 1 commits the changes.4. Transaction 2 updates customer 1's address.5. Transaction 2 updates customer 2's address.6. Transaction 2 commits the changes. D.1. A process is updating all rows of a large table in a batch transaction.2. There is an error that occurs while trying to update one of the records.3. The entire update has to be reverted.
C. Atomicity requires that all SQL requests in a transaction should be fully completed and if not, the entire transaction should be aborted. The transaction should be viewed as a single logical unit of work that is indivisible.
Which of the following scenarios reflect the atomicity property? a.)1. There are 50 desks available for purchase in the product table.2. In one transaction the customer purchases 10 desks.3. In the same transaction the customer purchases 10 desks.4. The validation check runs and sees there are 40 desks left.5. The entire transaction is reverted. b.) 1. An HR employee updates Jeff's salary by increasing it by 2.5%.2. The general manager updates Sally's salary by increasing it by 5%.3. The HR employee hasn't finished the transaction yet and had gotten on a call.4. The general manager updates Jeff's salary by increasing it by 5%.5. The HR employee's database session timed out.6. The database reverts Jeff's salary to what it was prior to the HR employee updating it and applies the 5% increase by the general manager. c.) 1. A user attempts to add an order to the database.2. They successfully add the customer, then add the order table.3. However, when trying to add the products to the order, the products no longer had any stock.4. The entire order has to be canceled. d.)1. Transaction 1 updates customer 1's address.2. Transaction 1 updates customer 2's address.3. Transaction 1 commits the changes.4. The changes are queued in the disk cover waiting to be committed to disk.5. The system fails and the changes are not written to memory.6. Once the database is started up again, the recovery management component saves the changes to memory.
D.
Which of the following scenarios reflect the consistency property? A. 1. Randall has updated the product table to set the price of an item to $99.2. The database fails after Randall commits the data.3. After the database starts back up, Randall checks the product and the price is set to $99 for the product he updated. B. 1. Transaction 1 updates customer 1's address.2. Transaction 1 updates customer 2's address.3. Transaction 1 commits the changes.4. Transaction 2 updates customer 1's address.5. Transaction 2 updates customer 2's address.6. Transaction 2 commits the changes. C. 1. A process is updating all rows of a large table in a batch transaction.2. There is an error that occurs while trying to update one of the records.3. The entire update has to be reverted. D. 1. A user attempts to do a bank transfer between accounts.2. Money withdrawn from the first account is performed.3. Only once the money is verified to have been withdrawn, the money is deposited into the second account.4. Verification is done again to ensure that the total amounts before and after the transaction are maintained.
C.
Which of the following scenarios reflect the consistency property? a.) 1. An HR employee updates Jeff's salary by increasing it by 2.5%.2. The general manager updates Sally's salary by increasing it by 5%.3. The HR employee hasn't finished the transaction yet and had gotten on a call.4. The general manager updates Jeff's salary by increasing it by 5%.5. The HR employee's database session timed out.6. The database reverts Jeff's salary to what it was prior to the HR employee updating it and applies the 5% increase by the general manager. b.) 1. A user attempts to add an order into the database.2. They successfully add the customer, then add the order table.3. However, when trying to add the products to the order, the products no longer had any stock.4. The entire order has to be canceled. c.)1. There are 50 desks available for purchase in the product table.2. In one transaction the customer purchases 10 desks.3. In the same transaction the customer purchases 50 desks.4. The validation check runs and sees there are 40 desks left.5. The entire transaction is reverted. d.) 1. Transaction 1 updates customer 1's address.2. Transaction 1 updates customer 2's address.3. Transaction 1 commits the changes.4. The changes are queued in the disk cover waiting to be committed to the disk.5. The system fails and the changes are not written to memory.6. Once the database is started up again, the recovery management component saves the changes to memory.
D. The consistency property ensures that a transaction takes the database from one consistent state to another consistent state. If any transaction part violates an integrity constraint, the entire transaction should be rolled back
Which of the following scenarios reflect the consistency property? a.)1. Multiple transactions are being executed separately but in parallel.2. All transactions are carried out and executed as if they were one transaction.3. No transaction will affect another transaction. b.)1. A transaction updates a chunk of data in a database and commits it.2. During the commit of the transaction, the system fails.3. Once the database is brought back online, the data is updated and written. c.)1. A transaction has five SQL requests.2. The first two successfully completed.3. The third one failed4. The entire transaction is rolled back. d.)1. A user attempts to remove a product from the product table.2. The product has been purchased by other individuals and exists in the order table.3. The transaction is denied due to a foreign key constraint.
creates an account that can bypass all restrictions
what does the below do in terms of superuser? CREATE ROLE admin account SUPERUSER LOGIN PASSWORD 'secretpassword';
D.
Which of the following scenarios reflect the durability property? a.) 1. An HR employee updates Jeff's salary by increasing it by 2.5%.2. The general manager updates Sally's salary by increasing it by 5%.3. The HR employee hasn't finished the transaction yet and had gotten on a call.4. The general manager updates Jeff's salary by increasing it by 5%.5. The HR employee's database session timed out.6. The database reverts Jeff's salary to what it was prior to the HR employee updating it and applies the 5% increase by the general manager. b.) 1. A user attempts to add an order into the database.2. They successfully add the customer, then add the order table.3. However, when trying to add the products to the order, the products no longer had any stock.4. The entire order has to be canceled. c.)1. There are 50 desks available for purchase in the product table.2. In one transaction the customer purchases 10 desks.3. In the same transaction the customer purchases 10 desks.4. The validation check runs and sees there are 40 desks left.5. The entire transaction is reverted. d.) 1. Transaction 1 updates customer 1's address.2. Transaction 1 updates customer 2's address.3. Transaction 1 commits the changes.4. The changes are queued in the disk cover waiting to be committed to the disk.5. The system fails and the changes are not written to memory.6. Once the database is started up again, the recovery management component saves the changes to memory.
A.
Which of the following scenarios reflect the durability property? a.)1. A transaction updates a chunk of data in a database and commits it.2. During the commit of the transaction, the system fails.3. Once the database is brought back online, the data is updated and written. b.) 1. A user attempts to remove a product from the product table.2. The product has been purchased by other individuals and exists in the order table.3. The transaction is denied due to a foreign key constraint. c.) 1. A transaction has five SQL requests.2. The first two successfully completed.3. The third one failed.4. The entire transaction is rolled back. d.)1. Multiple transactions are being executed separately but in parallel.2. All transactions are carried out and executed as if they were one transaction.3. No transaction will affect another transaction.
B.
Which of the following scenarios reflect the durability property? a.) 1. The new letter column can only accept Y or N as options.2. The user tries to attempt to enter in Maybe.3. The transaction is denied. b.)1. A transaction has completed executing.2. The changes are saved to volatile memory.3. The changes are then saved to non-volatile memory. c.)1. The product quantity starts at 200.2. Transaction 1 runs to read the quantity of the product to be updated.3. Transaction 2 attempts to read the quantity of the same product.4. Transaction 2 has to wait until transaction 1 is complete.5. Transaction 1 updates the product quantity to reduce it by 100.6. Transaction 2 is now able to read the quantity to be updated.7. Transaction 2 updates the product quantity to reduce it by 5.8. The product quantity ends at 95. d.)1. A transaction has two SQL requests.2. Both successfully complete.3. The data is then committed.
B
Which of the following scenarios reflect the isolation property? a.)1. A transaction has two SQL requests.2. Both successfully complete.3. The data is then committed. b.)1. The product quantity starts at 200.2. Transaction 1 runs to read the quantity of the product to be updated.3. Transaction 2 attempts to read the quantity of the same product.4. Transaction 2 has to wait until transaction 1 is complete.5. Transaction 1 updates the product quantity to reduce it by 100.6. Transaction 2 is now able to read the quantity to be updated.7. Transaction 2 updates the product quantity to reduce it by 5.8. The product quantity ends at 95. c.)1. A transaction has completed executing.2. The changes are saved to volatile memory.3. The changes are then saved to non-volatile memory. d.) 1. The new letter column can only accept Y or N as options.2. The user tries to attempt to enter in Maybe.3. The transaction is denied.
D.
Which of the following scenarios reflect the isolation property? a.)1. A transaction updates a chunk of data in a database and commits it.2. During the commit of the transaction, the system fails.3. Once the database is brought back online, the data is updated and written. b.) 1. A transaction has five SQL requests.2. The first two successfully completed.3. The third one failed.4. The entire transaction is rolled back. c.) 1. A user attempts to remove a product from the product table.2. The product has been purchased by other individuals and exists in the order table.3. The transaction is denied due to a foreign key constraint. d.)1. Multiple transactions are being executed separately but in parallel.2. All transactions are carried out and executed as if they were one transaction.3. No transaction will affect another transaction.
D
Which of the following sequence of statements would ensure that testuser has the privileges of the roles of technology and marketing? a.)CREATE ROLE marketing NOINHERIT; CREATE ROLE technology NOINHERIT; GRANT technology to testuser; GRANT marketing to technology; b.)CREATE ROLE marketing NOINHERIT; CREATE ROLE technology NOINHERIT; GRANT marketing to testuser; GRANT marketing to technology; c.)CREATE ROLE marketing NOINHERIT; CREATE ROLE technology INHERIT; GRANT technology to testuser; GRANT marketing to technology; d.)CREATE ROLE marketing INHERIT; CREATE ROLE technology INHERIT; GRANT technology to testuser; GRANT marketing to technology;
B
Which of the following statements represents a correctly structured transaction? a.)BEGIN UPDATE customerSET company='Telus', support_rep_id=6WHERE customer_id = 9 UPDATE customerSET phone='9991112222', email='[email protected]' WHERE first_name = 'Daan' AND last_name='Peeters'UPDATE employee SET reports_to = 1, title = 'IT Manager' WHERE last_name = 'King' AND first_name = 'Robert' COMMIT b.)BEGIN; UPDATE customerSET company='Telus', support_rep_id=6WHERE customer_id = 9; UPDATE customerSET phone='9991112222', email='[email protected]' WHERE first_name = 'Daan' AND last_name='Peeters'; UPDATE employee SET reports_to = 1, title = 'IT Manager' WHERE last_name = 'King' AND first_name = 'Robert'; COMMIT; c.)BEGIN; COMMIT; UPDATE customerSET company='Telus', support_rep_id=6WHERE customer_id = 9;UPDATE customerSET phone='9991112222', email='[email protected]'WHERE first_name = 'Daan' AND last_name='Peeters';UPDATE employeeSET reports_to = 1, title = 'IT Manager'WHERE last_name = 'King' AND first_name = 'Robert'; d.)BEGIN UPDATE customerSET company='Telus', support_rep_id=6WHERE customer_id = 9;UPDATE customerSET phone='9991112222', email='[email protected]'WHERE first_name = 'Daan' AND last_name='Peeters';UPDATE employeeSET reports_to = 1, title = 'IT Manager'WHERE last_name = 'King' AND first_name = 'Robert';COMMIT;
Generalized Search Tree (GiST)
this is a more complex type of index unique to PostgreSQL that is not used often but focuses on certain functionality like searching for a value closest to another value or finding pattern matching. As such, there are special types of operators that could be useful for this purpose like <<, &< &>, >>, <<|, &<|, |&>, |>> @<, @>, ~= and &&.
D.
Which of the following statements represents a correctly structured transaction? a.)BEGIN insert into album (artist_id, title, album_id) values (1, 'My Album', 348); insert into playlist (playlist_id, name) values (30, 'New Age Playlist'), (31, 'Oldies'), (32, 'Road Trip'); UPDATE albumSET title = 'Out of Exile [Disc 1]' WHERE album_id = 11; COMMIT; b.)BEGIN insert into album (artist_id, title, album_id) values (1, 'My Album', 348)insert into playlist (playlist_id, name) values (30, 'New Age Playlist'), (31, 'Oldies'), (32, 'Road Trip')UPDATE albumSET title = 'Out of Exile [Disc 1]'WHERE album_id = 11COMMIT c.)BEGIN; COMMIT; insert into album (artist_id, title, album_id) values (1, 'My Album', 348);insert into playlist (playlist_id, name) values (30, 'New Age Playlist'), (31, 'Oldies'), (32, 'Road Trip');UPDATE albumSET title = 'Out of Exile [Disc 1]'WHERE album_id = 11; d.)BEGIN; insert into album (artist_id, title, album_id) values (1, 'My Album', 348); insert into playlist (playlist_id, name) values (30, 'New Age Playlist'), (31, 'Oldies'), (32, 'Road Trip'); UPDATE albumSET title = 'Out of Exile [Disc 1]' WHERE album_id = 11; COMMIT;
C.
Which of the following statements will grant full permissions on the track table to the role musician? a.)GRANT ALL ON musician to TRACK; b.)GRANT ALL ON 'track' TO 'musician'; c.)GRANT ALL ON track TO musician; d.)GRANT FULL ON track TO musician;
C.
Which of the following statements would create a role named marketing and prevents the ability to inherit privileges from other group roles that it is a member of? a.)CREATE ROLE marketing; b.)CREATE ROLE marketing INHERIT; c.)CREATE ROLE marketing NOINHERIT; d.)CREATE ROLE marketing CREATEROLE;
B
Which of the following statements would create a user named davida with the password set as 'j3SoFH3'? a.)CREATE USER davida PASSWORD 'j3SoFH3' b.)CREATE USER davida WITH PASSWORD 'j3SoFH3' c.)CREATE USER 'davida' WITH PASSWORD 'j3SoFH3' d.)CREATE USER 'davida' WITH PASSWORD j3SoFH3
A
Which of the following statements would generate a database backup file correctly while recreating an entire database cluster including roles and tablespaces that it was at the time of the dump from the mydatabase database to a file named backup.sql? a.)pg_dumpall > backup.sql b.)pg_dumpall < backup.sql c.)pg_dump mydatabase < backup.sql d.)pg_dump mydatabase > backup.sql
A
Which of the following statements would generate a database backup file correctly while recreating an entire database cluster including roles and tablespaces that it was at the time of the dump from the mydatabase database to a file named backup.sql? a.)pg_dumpall > backup.sql b.)pg_dumpall < backup.sql c.)pg_dump mydatabase < backup.sql d.)pg_dump mydatabase > backup.sql
A.
Which of the following statements would include large objects of the data in a data dump of the mydatabase to backup.sql? A. pg_dump -b mydatabase > backup.sql B. pg_dump -d mydatabase > backup.sql C.pg_dump mydatabase > backup.sql D.pg_dump -a mydatabase > backup.sql
D.
Which of the following statements would only dump the data and not the data definitions of the mydatabase to backup.sql? a.)pg_dump -d mydatabase > backup.sql b.)pg_dump mydatabase > backup.sql c.)pg_dump -b mydatabase > backup.sql d.)pg_dump -a mydatabase > backup.sql
D
Which of the following statements would only dump the data and not the data definitions of the mydatabase to backup.sql? a.)pg_dump -d mydatabase > backup.sql b.)pg_dump mydatabase > backup.sql c.)pg_dump -b mydatabase > backup.sql d.)pg_dump -a mydatabase > backup.sql
A
Which of the following statements would restore a database backup file correctly from backup.sql to mydatabase on the hostname newhost? a.)psql -h myhost mydatabase < backup.sql b.)psql -f backup.sql mydatabase c.)psql -h myhost mydatabase > backup.sql d.)psql myhost mydatabase < backup.sql
C.
Which of the following statements would restore a database backup file correctly from backup.sql to mydatabase? a.)psql mydatabase > backup.sql b.)psql backup.sql > mydatabase c.)psql mydatabase < backup.sql d.)psql backup.sql < mydatabase
A
Which of the following statements would restore a database backup file named backup.sql that includes roles and tablespaces to an empty database cluster? a.)pg_restore -f backup.sql postgres b.)pg_restore mydatabase > backup.sql c.)pg_restore mydatabase < backup.sql d.)pg_restore -f backup.sql mydatabase
A
Which of the following type of indexes can only handle simple equality comparisons? a.) Hash b.) B-tree c.)BRIN d.)GIN
B
Which type of backup would back up data only since the previous backup? a.) Nightly backup b.) Incremental backup c.) Full backup d.) Differential backup
D
Which type of backup would back up data since its previous full backup even if other backups have been created? a.) Nightly backup b.) Incremental backup c.) Full backup d.) Differential backup
Hash
____ indexes can only handle simple equality operators and in most cases will be the first index to use when we use the = operator.
Group
____ roles would not have a LOGIN attribute or password as users are meant to inherit the permissions from the _____ role as a best practice.
indexes
______ are used to speed up queries and avoid having to search through data one row at a time.
CREATEDB
attribute just defines if the role is able to create databases. Typically a database administrator role would benefit from this attribute.
ROLLBACK
if we accidentally made changes to the database that didn't throw an errors but is still incorrect how can we undo this?
